Why use Triggers?

Using triggers can greatly improve data integrity. When an action is performed on data, it is possible to check if the manipulation of the data concurs with the underlying business rules, and thus avoid erroneous entries in a table.

For example:

• We might want to ship a free item to a client with the order, if it totals more than $1000. A trigger will be built to check the order total upon completion of the order, to see if an extra order line needs to be inserted.

• In a banking scenario, when a request is made to withdraw cash from a cash point, the stored procedure will create a record on the client's statement table for the withdrawal, and the trigger will automatically reduce the balance as required. The trigger may also be the point at which a check is made on the client's balance to verify that there is enough balance to allow the withdrawal. By having a trigger on the statement table, we are secure in the knowledge that any statement entry made, whether withdrawal or deposit, will be validated and processed in one central place.

Note that we discuss only data integrity here, and not referential integrity.

We may also wish to replicate changes in one database into another; a trigger on the table that is being updated will be ideal to place the changes from the ‘live’ database to the ‘audit’ database. We use this scenario to create an audit on specific tables, but the audit is kept in a separate database. When we discuss building triggers, in the Creating and Using a Trigger section, we will see this scenario in action. This is different from replication, which we will discuss later in the Replication section.

Another use of trigger can be to carry out an action when a specific criterion has been met. One example of this is a case where an e-mail requesting more items to be delivered is sent, or an order for processing could be placed, when stock levels reach a preset level. However, if we insert data into another table from within a trigger, we have to be careful that the table we insert into doesn't have a trigger that will cause this first trigger to fire. It is possible to code triggers that result in an endless loop, as we can define a trigger on TableA, which inserts into TableB, and a trigger for TableB, which updates TableA. This scenario will ultimately end in an error being generated by SQL Server. The following diagram will demonstrate this:

Figure 1

1. A stored procedure, A, updates TableA.

2. This fires a trigger from TableA.

3. The defined trigger on TableA updates TableB.

4. TableB has a trigger which fires.

5. This trigger from TableB updates TableA.