Triggers on MS SQL Tables to create an Audit Trail

Posted on 2005-05-02
Medium Priority
Last Modified: 2013-01-22
I am trying to create an audit trail for my database and I need some direction.

I have a database that is used for job scheduling.  The concept of a job is pretty complex (lots of variables) and was thus split into about 27 different tables that all tie together with a combination of JOB_ID and TABLE_ID.  An update to a job might be run against any 1 or more of those 27 tables depending on what needs to be changed.

I need to be able to track these changes.  I need to create an audit reporting table that contains:
Date of change
Job being changed (JOBNAME column of the DEF_JOB table where JOB_ID and TABLE_ID of the changed value match the DEF_JOB's JOB_ID and TABLE_ID - this links the master job table (one of the 27 tables that make up a job) to whatever was changed)
User making the change
Table/Field changed
Old Value
New Value.

So my trigger needs to be able to write all that information back to the audit table.  Does ANYBODY know how I can do this, or even if I can?  I can supply the schema if it helps.

Question by:JRamos1200
LVL 30

Accepted Solution

nmcdermaid earned 1000 total points
ID: 13914409
Create a table to contain your log, with fields as you have specified.

Date of change - Default should be GETDATE()
Job being changed - Set from table trigger
User - Default should be SYSTEM_USER
Table/Field changed - Set from table trigger
Old Value - Set from table trigger
New Value - Set from table trigger

Create an UPDATE trigger on each table.

Within these triggers insert the record into your log table.

Be aware of the following:

-SYSTEM_USER returns the SQL Server login so if everyone is logging in as the same thing, this is no good.
-If multiple rows are updated, the update trigger will be called ONCE for all rows

You may need more detail that this so let me know if you need help.
LVL 34

Assisted Solution

arbert earned 1000 total points
ID: 13915096
Nigel has some really good generic triggers on his site that will give you a pretty good direction on what you need to write for triggers:



Author Comment

ID: 13917495
nmcdermaid ,

Indeed I would like more information.  I have never created a trigger before so have no understnading of the syntax of the trigger itself, or of how to pull the info out of the "trigger table" what ever that is!

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 13917501

Thanks for the link.  I am reading through it.
LVL 30

Expert Comment

ID: 13922234
If you look up 'CREATE TRIGGER' in books online it'll give you some dry (but thorough) explanations.

The link will probably have the whole thing explained.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14102150
Split the points.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question