March 25, 2010 at 1:25 pm
What is the difference between FOR and AFTER Update triggers and how to understand the following phrase from the msdn library (http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx)
"AFTER is the default, if FOR is the only keyword specified."
Thanks!
March 25, 2010 at 1:30 pm
If you use the keyword FOR, you will get an AFTER trigger.
Best bet, declare triggers as either AFTER or INSTEAD OF, don't use the FOR.
March 25, 2010 at 1:36 pm
FOR is for an action (Insert, Update, whatever). AFTER waits for all parts of the SQL clause to complete, that is for all conditions to have been met. Both will peform after the watched activity. FOR and AFTER mean the transaction you were concerned with have already occured, thus will potentially need to be rolled back.
INSTEAD OF will fire at the same time and will not require, necessarily, for the transaction of interest to be rolled-back.
For Example...
CREATE TRIGGER test /* change to ALTER when you edit this trigger */
ON tbltest
FOR INSERT, UPDATE /* Fire this trigger after a row is INSERTed or UPDATEd */
AS
BEGIN
UPDATE tblTest SET tbltest.testvalue=tbltest.name
FROM INSERTED
WHERE inserted.id=tbltest.id
END
March 25, 2010 at 1:42 pm
Thanks, Lynn!
Does this mean that the keywords AFTER and FOR have the same meaning?
If so why one of them is better than other?
March 25, 2010 at 1:52 pm
Always Learning (3/25/2010)
FOR is for an action (Insert, Update, whatever). AFTER waits for all parts of the SQL clause to complete, that is for all conditions to have been met. Both will peform after the watched activity. FOR and AFTER mean the transaction you were concerned with have already occured, thus will potentially need to be rolled back.INSTEAD OF will fire at the same time and will not require, necessarily, for the transaction of interest to be rolled-back.
For Example...
CREATE TRIGGER test /* change to ALTER when you edit this trigger */
ON tbltest
FOR INSERT, UPDATE /* Fire this trigger after a row is INSERTed or UPDATEd */
AS
BEGIN
UPDATE tblTest SET tbltest.testvalue=tbltest.name
FROM INSERTED
WHERE inserted.id=tbltest.id
END
CREATE TRIGGER test
ON tbltest
FOR UPDATE /* Fire this trigger after a row is UPDATEd */
and
CREATE TRIGGER test
ON tbltest
AFTER UPDATE /* also will Fire this trigger after a row is UPDATEd */
Both will peform after the watched activity.
is there a difference between these 2 triggers?
March 25, 2010 at 1:58 pm
Nope, they will both function essentially the same. AFTER is generally used (in my experience it may have other, better uses) when there are multiple SQL statements and you want them all to have completed or committed before performing the trigger action. FOR would apply to the single statement of interest. I don't know if there is a peformance difference.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply