Triggers | Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features

The SQL Server 2005 trigger is secondary to the primary built-in mechanisms for enforcing business rules and data integrity of any application or enterprise requirement, as we discussed in the preceding chapter. A trigger, however, is a lot more than a constraint check or a rule; it packs a lot more punch.

A trigger can do a lot. For all intents and purposes, it is a stored procedure that in itself is a full-blown SQL statement or batch that can query tables, calculate, evaluate, communicate, and provide complex functionality. SQL Server relates to a trigger in the same way it relates to an inline SQL statement that comes down the TDS wire. Triggers are treated as single transactions, which means that if they create an undesirable result, they can be rolled back (see Chapter 16).

Triggers are used in the following situations:

As cascades Triggers can be used to cascade events and changes through related tables in a database. A good example is the manual cascade deletes or updates we would program in triggers in earlier versions of SQL Server (pre-Y2K) before these wishes became de facto features in SQL Server 2000 (the prodigy parent of SQL Server 2005). In many cases, cascading triggers can be used in data integrity or business rule requirements. You should, however, only consider this if the built-in cascade features and declarative integrity functions do not supply the desired end result or don’t exist. Naturally the built-in stuff is more efficient (referential integrity is a good example of a constraint effort that would be wasted in a trigger).
As checks (and then some) Triggers can do the work of the check constraints we discussed in Chapter 12, but when you need more bang for your buck, triggers come to take the lead. A trigger, created as a super constraint, is essentially a check constraint on steroids. You can reference columns in other tables (which you can’t do on a check definition). You can conjure up a result set out of a trigger and then run through the result check to analyze inserted or modified data. Triggers can also talk to your users, fire off e-mail, or wake the DBA in the middle of the night. However, while constraints are proactive “filters” so to speak, triggers are reactive processes. Even the INSTEAD OF trigger fires in reaction to the DML statement sent to SQL Server.
As rule enforcers Like badgers to honey, the existence of a database will invariably tempt users to attempt contradictory data access procedures on your data. Some users may just do things that the developer or DBA did not expect, while others may attempt to access the database in ways contrary to corporate or organization rules…and in many cases with criminal intent. A trigger can be used to ensure that a certain action cannot be attempted on a table. For example, an attempt to retrieve all credit card numbers from a table can be blocked in a trigger. The trigger can also send out alerts and even capture connection information and perform certain auditing functions. (This chapter includes the code for a highly efficient database object auditing system.) And because you can install multiple triggers on a table, you can pretty much take care of any situation that is contrary to both business rules and change control procedures.
As an evaluation mechanism You can use a trigger to evaluate the state of your data before and after a DML statement does its work on a table or view. If the state is not up to par or compliance, then a trigger can be used to fill in the “missing links” or take some corrective action, such as rolling back, without requiring additional user input. Here’s a drastic, but entirely possible, sequence of events a trigger can initiate:
```
 CREATE TRIGGER Self_Destruct   ON SwissCashAccounts     FOR UPDATE     AS       IF Update(IntentToSteal)         EXEC DotNetMailer 'Users,' 'This server will self-destruct         in five seconds'      WAITFOR DELAY '00:00:05'      EXEC vaporize_server
```

An important attribute of triggers is that they have a long reach. While checks and rules are limited to tables in the current database, a trigger can reference beyond its parent table to other tables and other databases. But all good things have their limitations.