Good Practice

The most important point is to keep the trigger as short as possible to execute quickly, just like stored procedures. The longer a trigger takes to fire, the longer the locks will be held on the underlying tables. To this end, we could place cursors within a trigger, but good practice dictates that we don't. Cursors are not the fastest of objects within a database, and we should try and revisit the problem with a different solution, if we feel the need for cursors. One way around the problem may be to run two, or perhaps, three updates, or even use a temporary table instead.

Use triggers to enforce business rules, or to complete actions that have either a positive effect on the organization, or if an action will stop problems with the system. An example of this is creation of a trigger that will e-mail a client when an order is about to be shipped, giving details of the order, and so on.

Note

Use an @@ROWCOUNT, where required, to check the number of rows that have been affected.

If we have a trigger on a table, watch out if our code invoking the trigger uses the @@IDENTITY value. If we have a stored procedure that inserts an order and returns @@IDENTITY to give the order number created, and we have an audit trigger that inserts into the audit table, which also has an IDENTITY column, the @@IDENTITY value returned by the stored procedure will be that from the audit table and not the orders table. To get around this, we will perhaps have to use a MAX() function to find the last order ID.