FOR/AFTER in SQL triggers

Question

I am newbie in SQL. I am reading about Triggers in SQL.I have got almost about Triggers. But in DML Triggers, we use FOR/AFTER keyword. I didn't get difference between FOR/AFTER and why we use FOR/AFTER keyword. I have already read on MSDN but didn't get the simple answer. Can anyone explain me what is it?

Thanks in advance.

Answer 1

There is no difference between using FOR and AFTER.

I believe the original (pre 2000) syntax only used the FOR keyword. However, when INSTEAD OF triggers were introduced, the "FOR" keyword could seem quite confusing. "AFTER" more accurately conveys the type of trigger, and is more easily distinguished from "INSTEAD OF".

---

An INSTEAD OF trigger would be used if we wanted to transform what was inserted into the table, or prevent an insertion from taking place.

An AFTER trigger would more normally be used if we wanted to perform additional tasks, based on what has just occurred. For instance, you could have an "AFTER DELETE" trigger, that copied deleted rows into some kind of archive table. Basically, in an AFTER trigger, you more normally do still want the activity to occur.

Answer 2

From MSDN:

AFTER triggers are never executed if a constraint violation occurs; therefore, these triggers cannot be used for any processing that might prevent constraint violations.

And then:

You can request AFTER triggers by specifying either 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 there is no difference.