Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Triggers for Auditing

By Tiago Andrade e Silva,

A Trigger 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.

Total article views: 11961 | Views in the last 30 days: 12
 
Related Articles
FORUM

Insert trigger not working when update trigger enabled

Update interfering with insert trigger.

FORUM

Trigger defined for Insert, Update

Determine Update or Insert for Trigger defined for Insert, Update

FORUM

not updating inserted row in an insert trigger

not updating inserted row in an insert trigger

FORUM

How to List All table Modified, altered, updated, inserted in sqlserver 2000 database Last N Days.

How to List All table Modified, altered, updated, inserted in sqlserver 2000 database Last N Days.

FORUM

Help with update trigger

help with update trigger that can update a table from any changes to a view

Tags
triggers    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones