Use of Triggers

The use of triggers in SQL Server 2000 represents an important part of process development (see Chapter 8). INSERT triggers are used to modify and disallow data during an attempt to change a record. UPDATE triggers are used to restrict or allow update modifications made to a certain table. DELETE triggers work in reverse to how INSERT triggers work. They restrict the removal of data from a table, whereas an INSERT trigger restricts the data that goes inside a table.

Recursive Triggers

Triggers are used to keep the flow of data within the boundaries of proper business logic, and this is why they are so widely used. Their capability is equal to stored procedures, and they are able to change records in other tables. There are two types of recursion, and each type of recursion has a solution within the SQL Server. The two types of recursion are direct and indirect. Before any recursion takes place, the database option of Recursive Triggers must be set to On. This option can be set with sp_dboption and defaults to Off.

INSTEAD OF Triggers

Prior to SQL Server 2000, the only type of trigger available was an AFTER trigger ”a trigger that contains a set of statements that fire after a modification to a table, after constraints have been applied. INSTEAD OF triggers are newly introduced into SQL Server 2000 and execute instead of the triggering action.

Because there can be only one INSTEAD OF trigger of each type on a table, there is no need or reason to attempt to set the fire order of INSTEAD OF triggers. There can be many AFTER triggers of each type set on a single table, however, which means that there is a reason to set the order of triggers. To change the fire order of triggers, you can use the sp_settriggerorder stored procedure. With the exception of first and last, you have no control over how triggers will fire.