FOR / AFTER in SQL triggers

Question

I am new to SQL. I am reading about triggers in SQL. I have almost triggers. But in DML Triggers, we use the FOR / AFTER keyword. I did not get the difference between FOR / AFTER and why we use the FOR / AFTER keyword. I already read on MSDN but didn't get a simple answer. Can someone explain to me what this is?

Thanks in advance.

Answer 1

There is no difference between using FOR and AFTER.

I believe that the original (pre 2000) syntax used only the FOR keyword. However, when INSTEAD OF triggers were introduced, the keyword “FOR” may seem rather confusing. AFTER conveys the trigger type more accurately and differs more easily from INSTEAD OF.

---

The INSTEAD OF trigger will be used if we want to convert what was inserted into the table, or to prevent insertion.

The AFTER trigger will be more commonly used if we want to perform additional tasks based on what just happened. For example, you might have a “AFTER DELETE” trigger that copied deleted rows to some kind of archive table. Basically, in an AFTER trigger, you usually still want the activity to happen.

Answer 2

From MSDN :

AFTER triggers are never executed if a restriction violation occurs; therefore, these triggers cannot be used for any processing that may prevent restrictions being violated.

And then :

You can request AFTER triggers by specifying the AFTER or FOR keywords. Because the FOR keyword has the same effect as AFTER, DML triggers with the FOR keyword are also classified as AFTER triggers.

It would seem that there is no difference.

Answer 3

If I correctly interpret your comments on other answers, you want to know why or when you use the keywords "FOR | AFTER".

It's simple: there are two types of triggers: an AFTER trigger and an INSTEAD-OF trigger.
INSTEAD-OF trigger, for example. insert action can be written as

create trigger myTrigger on myTable INSTEAD OF insert begin (... code goes here ...) end 

and the AFTER trigger can be written as

 create trigger myTrigger on myTable AFTER insert begin (... code goes here ...) end 

or

 create trigger myTrigger on myTable FOR insert begin (... code goes here ...) end 

As Damien_The_Unbeliever mentions, the AFTER keyword is more readable than the FOR version, that's all.

Answer 4

They are the same. See this excerpt from the BOL "FOR | AFTER AFTER" indicates that a DML trigger is triggered only when all operations specified in the initiating SQL statement have completed successfully. All relational cascading actions and constraint checks must also succeed before this trigger is run.

AFTER is the default when FOR is the only keyword.

AFTER triggers cannot be defined in views. "

Answer 5

According to what I observe, FOR is used in the DDL trigger, and AFTER is used in the DML triggers. They have the same way of working.