Specifying When a DML Trigger Fires
You can specify one of two options to control when a DML trigger fires:
AFTER triggers fire after the triggering action (INSERT, UPDATE, or DELETE), INSTEAD OF triggers and constraints are processed. 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.
INSTEAD OF triggers fire in place of the triggering action and before constraints are processed. If there are AFTER triggers on the table, they will fire after constraint processing. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed.
Each table or view can have one INSTEAD OF trigger for each triggering action (UPDATE, DELETE, and INSERT). A table can have several AFTER triggers for each triggering action.
Examples
A. Using the INSTEAD OF trigger to replace the standard triggering action
CREATE TRIGGER TableAInsertTrig ON TableA
INSTEAD OF INSERT
AS ...
B. Using the AFTER trigger to augment the standard triggering action
CREATE TRIGGER TableBDeleteTrig ON TableB
AFTER DELETE
AS ...
C. Using the FOR trigger to augment the standard triggering action
-- This statement uses the FOR keyword to generate an AFTER trigger.
CREATE TRIGGER TableCUpdateTrig ON TableC
FOR UPDATE
AS ...