Write an Audit Trail

This last example shows you how to maintain an audit trail of changes to a column in a table. We’ll add an update trigger to the titles table to record any changes to the price column.

Table Creation Statements

You need an audit table where changes are recorded:

create     table   price_changes
(title_id        tid                                  not null,
 spid               int                               not null,
 change_date          datetime     default getdate()  not null,
 usr                     varchar(30) default user     not null,
 old_price          money                             null,
 new_price          money                             null
)

As you can see, default values have been specified for three of the columns. You can specify the others in the INSERT statement in the trigger.

Trigger Code

The trigger is much less complex than the last one. First, check to see if the price was updated. If it was, the trigger records the updates to the audit table, price_changes.

create      trigger      tr_titles_u_1
on      titles
for     update
as
if  @@rowcount  =  0  return
if     update(price)
begin
        insert  price_changes  (title_id,  spid,  old_price, new_price)
        select  i.title_id,  @@spid,  d.price,  i.price
        from   inserted i,  deleted d
        where   i.title_id  =   d.title_id
        and  i.price     !=  d.price
   if @@error  <>  0
   begin
             raiserror (“Audit  insert  failure:  rolling  back changes”,
           16,  1)
          rollback  transaction
      return
   end
end
return

The first test looks to see if the column has changed.

if     update(price)

Remember that the update() test only checks to see if the price column was named, not if the column was actually modified by the query. That’s why the INSERT…SELECT statement inserts rows only where the price inserted was different from the price deleted.

insert  price_changes  (title_id,  spid,  old_price,  new_price)
select  i.title_id,  @@spid,  d.price,  i.price
     from  inserted  i,  deleted  d
   where    i.title_id  =  d.title_id
     and    i.price      !=  d.price  --  <<<< this condition  filters  out
                                            extra data

It’s useful to note how much simpler a single-purpose trigger is. The code does not need to deal with the possibility that there are no corresponding rows between the inserted and deleted tables.

When to Use Triggers

You’ve learned about how to write triggers, and you've had some guidance on making them fast and fairly unobtrusive. Let’s try to draw all this advice together, along with some ideas on when triggers are a good idea and when they are not.

You have two broad choices to make in the implementation of MS SQL databases. You can write all your table modifications using standard SQL (or using ODBC, which is translated to SQL), or you can package those modifications into stored procedures. If you use stored procedures to handle all data modifications, and if you revoke all direct privileges to insert, update, and delete rows in all tables, you probably won’t need triggers. Everything you can do in a trigger after a row is modified, you can do in a procedure before it’s modified. In many circumstances, you get better performance, too.

The problem is that a lot of client/server development products use a flavor of ODBC that doesn’t support procedures, or they make it so impossibly complicated to address procedures that you really are back to standard T-SQL. In that case, you need to protect your data against bad SQL (like rolling back after an RI violation), and you need to perform dependent actions (like maintaining an audit table). Both of these kinds of operations are supported best through triggers.

Triggers become a part of the table, attaching themselves to one or more of the standard data operations against that table. Once you have created a trigger for an operation on a table, that operation simply cannot occur without the trigger firing.

Does that mean that all data validation should be performed in a trigger? Absolutely not. Check constraints allow you to check the value of a column against a literal range or condition, or to compare two columns to each other. They are far faster than triggers because there is no logging component to constraint execution. Use indexes to check for uniqueness.

Many users write triggers on a table that modify that table. Try to avoid this. This kind of work should be done in a procedure, if at all possible, or in the batch that performs the original data modification. Using a trigger to remodify a row costs at least double, and sometimes triple, the logging, and it casts triple the work in memory and disk.

Here’s a list of dos and don’ts with triggers.