Triggers are routines that fire automatically as a result of an event in the database server. Microsoft SQL Server supports data-manipulation language (DML) and data-definition language (DDL) triggers, which can be developed either with T-SQL or with .NET code and can fire AFTER or INSTEAD OF the triggering event. In this chapter, I’ll describe in detail the different types of triggers, first describing DML AFTER triggers and later, in a dedicated section, DDL AFTER triggers. I’ll also focus on AFTER triggers that you write with T-SQL, and in a dedicated section, I’ll describe triggers that you develop with .NET code (CLR triggers).

Triggers are first and foremost stored procedures in terms of how SQL Server treats them internally. They undergo similar processing phases to stored procedures (parsing, resolution, and optimization). However, triggers do not have an interface (input and output parameters), and you cannot invoke them explicitly. They fire automatically as a result of a statement submitted against the database server.

Triggers are part of the transaction that fired them. That is, the transaction is not considered complete until the trigger also finishes running. If you issue a ROLLBACK TRAN within the trigger’s code, you effectively roll back the trigger’s activity as well as all the activity of the transaction to which the trigger belongs. The rollback will undo all activity as of the outermost BEGIN TRAN statement, if one was explicitly issued. If the firing statement was not issued in an explicit transaction, a rollback in a trigger will undo all activity issued within the trigger until the ROLLBACK TRAN statement, as well as the activity of the firing statement.

Keep in mind that if you issue a rollback within the trigger (just as with any transaction), it’s as if the activity were performed twice–done and then undone. If your trigger is intended to enforce an integrity rule and you can achieve the task by prevention rather than reaction, you should do so to get better performance. For example, if you can enforce the integrity rule with a constraint, use the constraint. If you cannot enforce it with a constraint, see whether you can enforce it with a stored procedure that first performs validation before it determines whether to apply the change. Use triggers only if you can’t enforce the rules with constraints.

Triggers allow you to automate the process of reacting to statements issued by users and applications (AFTER triggers) or substituting the original statement with your own code (INSTEAD OF triggers). You can react to or substitute DML activities (INSERT, UPDATE, and DELETE) or react to DDL activities (CREATE, ALTER, and DROP). You can develop triggers with T-SQL or with .NET code. SQL Server 2005 does not support SELECT triggers, row-level triggers or BEFORE triggers. INSTEAD OF triggers are the closest you can get in SQL Server to BEFORE triggers.

You can use triggers to enforce complex integrity rules, to audit changes, and to do much more.