Triggers for Auditing

Have you ever had the need to know when a given row in a table was last changed? Maintaining a last_modified date field at the application level would give you more work and would not be the most correct way because somebody might just open Enterprise Manager and change the data directly.

In order to implement this functionality at the database level and never more worry about the update of that field we will need to use Triggers. A trigger is a set of statements that automatically executes after a data modification takes place on a table. We can register triggers for the 3 events that can occur on a table: Insert, update and delete.

In our case we would only need to create an UPDATE trigger in order to keep the last_modified field updated. Two approaches can be taken regarding the creation date, either we put a default value that uses the getdate() function, or we leave it null indicating it was never changed.

Available to the triggers is a virtual table that has the data that is about to be deleted or inserted or both in case of the update. If it is an update trigger, the deleted table has the record as it was before the update, and the inserted has the values that we are about to change.

One might be tempted to think that we would just need to change the last_modified field at the end of the trigger with the actual time and date and the magic would be done. But it's not so simple! The normal triggers which are called AFTER Triggers do not allow you to change the data on the virtual tables. So SQL Server 2000 has extended the power of triggers by letting you create a second kind of trigger, called an INSTEAD OF.

What the INSTEAD OF trigger does is override the normal insert,delete or update statement and leave that responsibility to your Trigger. You will be responsible for inserting, deleting or updating the data to the database.

Imagine we have a table with a last_modified column:

CREATE TABLE [Table_Name] (
           …
            [last_modified] [datetime] NULL DEFAULT (getdate()),
            …
) ON [PRIMARY]
GO

The trigger to automatically update the last_modified date column would be:

CREATE TRIGGER TriggerName ON [Table_Name]
INSTEAD OF UPDATE
AS
   UPDATE [Table_Name]

      SET  field1=(select field1 from inserted),
      		'Put Here all the fields of the table
          last_modified=getdate() 'this will do the magic
   WHERE id =(select id from inserted)
   

Conclusions

If you change the row you will notice that the field is automatically updated. If you manually change the last_modified your changes will be overridden by the actual time and date.