Database Reference
In-Depth Information
code is run. For example, if a process issues an insert to add a new em-
ployee to a table, the insert triggers the trigger. The code in the trigger is
run after the insert as part of the same transaction that issued the insert.
Managing transactions is a bit beyond the scope of this topic, but you
should know that because the trigger is run in the same context as the
DML statement, you can make changes to the affected data, up to and in-
cluding rolling back the statement. AFTER triggers are very useful for ver-
ifying business rules and then canceling the modification if the business
rule is not met.
During the execution of an AFTER trigger, you have access to two vir-
tual tables—one called Inserted and one called Deleted. The Deleted
table holds a copy of the modified row or rows as they existed before a
delete or update statement. The Inserted table has the same data as the
base table has after an insert or update. This arrangement allows you to
modify data in the base table while still having a reference to the data as it
looked before and after the DML statement.
These special temporary tables are available only during the execution
of the trigger code and only by the trigger's process. When creating
AFTER triggers, you can have a single trigger fire on any combination of
insert, update, or delete. In other words, one trigger can be set up to run
on both insert and update, and a different trigger could be configured to
run on delete. Additionally, you can have multiple triggers fire on the same
statement; for example, two triggers can run on an update. If you have
multiple triggers for a single statement type, the ordering of such triggers
is limited. Using a system stored procedure, sp_settriggerorder, you can
specify which trigger fires first and which trigger fires last. Otherwise, they
are fired in the middle somewhere. In reality, this isn't a big problem. We
have seen very few tables that had more than two triggers for any given
DML statement.
INSTEAD OF triggers
are a whole different animal. These triggers
perform in the way you would expect: The code in an INSTEAD OF trigger
fires in place of the DML statement that caused the trigger to fire. Unlike
AFTER triggers, INSTEAD OF triggers can be defined on views as well as
tables. Using them, you can overcome the limitation of views that have mul-
tiple base tables. As mentioned earlier, you can update a view only if you
limit your update to affecting only a single base table. Using an INSTEAD
OF trigger, you can update all the columns of a view and use the trigger to
issue the appropriate update against the appropriate base table. You can also
use INSTEAD OF triggers to implement advanced data integrity or busi-
ness rules by completely changing the action of a DML statement.
Search WWH ::
Custom Search