Triggers are
code that are automatically executed when certain events occur. There are two
broad categories of triggers: (1) FOR/AFTER triggers, and (2) INSTEAD OF
triggers.
The FOR
trigger is straight forward. It is also called an AFTER trigger. When you issue
an INSERT, UPDATE, or DELETE, those operations act on the data. Then the AFTER
trigger is fired. That gives you a chance to rollback the changes made by the
DML.
The INSTEAD
OF trigger works differently. The data changes from the original DML are not
made. The INTEAD OF trigger will fire. You can then do some other work. This is
useful for a view, where there are limitations on what you can do with DML.
Triggers can be nested. The outer DML can cause
a trigger to fire, which in turn executes other DML that itself causes another
trigger to fire. Within the trigger you can access the @@NESTLEVEL system
variable to check how deep you are within nested trigger calls. It has a value
of 0 for the outer DML code. It is 1 if you are in the first trigger, 2 for
code called by the first trigger, and so on. The maximum depth of calls is 32.
