Understanding Triggers

Triggers are a special class of stored procedure defined to execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table. Triggers are a powerful tool that allows each site to enforce their business rules automatically when data is modified. Triggers can extend the integrity checking logic of MSSQL constraints, defaults, and rules, although constraints and defaults should be used instead whenever they provide all the needed functionality.

Tables can have multiple triggers. The CREATE TRIGGER statement can be defined with the FOR UPDATE, FOR INSERT, or FOR DELETE clauses to target a trigger to a specific class of data modification actions. When FOR UPDATE is specified, the IF UPDATE (column_name) clause can be used to target a trigger to updates affecting a particular column. SQL Server allows you to specify multiple triggers for a specific action (UPDATE, INSERT, or DELETE) on a single table.

Triggers can automate a company’s processing. In an inventory system, update triggers can detect when a stock level reaches a reorder point and generate an order to the supplier automatically. In a database recording the processes in a factory, triggers can e-mail or page webmaster when a process exceeds defined safety limits.

The following trigger generates an e-mail whenever a new title is added in the pubs database.

CREATE TRIGGER reminder ON titles FOR INSERT AS EXEC master..xp_sendmail 'MaryM', 'New title, mention in the next report to distributors.'

Triggers contain Transact-SQL statements, much the same as stored procedures. Triggers, like stored procedures, return the result set generated by any SELECT statements in the trigger. Including SELECT statements in triggers, except ones that only fill parameters, is not recommended because users do not expect to see any result sets on an UPDATE, INSERT, or DELETE statement.

Triggers execute after the statement that triggered them completes. If the statement fails with an error, such as a constraint violation or syntax error, the trigger is not executed.

Triggers and Rollback Transaction

When triggers that include ROLLBACK TRANSACTION statements are executed from a batch, they cancel the entire batch. In the following example, if the INSERT statement fires a trigger that includes a ROLLBACK TRANSACTION, the DELETE statement is not executed because the batch is canceled:

INSERT employee VALUES ('XYZ12345M', 'New', 'M', 'Employee', 1, 1, '9952', '6/1/95') -- Causes trigger to fire and ROLLBACK TRANSACTION DELETE employee WHERE emp_id = 'PMA42628M'

If triggers that include ROLLBACK TRANSACTION statements are fired from within a user-defined transaction, the ROLLBACK TRANSACTION rolls back the entire transaction.