?
Solved

AuditTrail trigger on multiple columns / SQL condition loop?

Posted on 2007-08-02
4
Medium Priority
?
281 Views
Last Modified: 2010-03-19
Hi all.. I'm creating an Audit Trail trigger to capture any changes on one of my database tables. The method I am using is to check each column in the table to see if there are any changes then to write the UserID, TableName, ColumnName, LastModifiedDate, LastModifiedUser, OldValue, NewValue to the audit table.. as per code snippet below;

------------------------------------
-- Audit BadgeName Changes, if any
INSERT INTO AuditTable (UserID, TableName, ColumnName, LastModifiedDate, LastModifiedUser, OldValue, NewValue)
SELECT      I.UserID, 'Users', 'Badge Name', I.LastModifiedDate, I.LastModifiedUser, D.UserBadgeName, I.UserBadgeName
FROM      Inserted I
      JOIN Deleted D ON I.UserID = D.UserID
WHERE      D.UserBadgeName <> I.UserBadgeName

-- Audit UserEmailAddress Changes, if any
INSERT INTO AuditTable (UserID,TableName, ColumnName, LastModifiedDate, LastModifiedUser, OldValue, NewValue)
SELECT      I.UserID, 'Users', 'User EmailAddress', I.LastModifiedDate, I.LastModifiedUser, D.UserEmailAddress, I.UserEmailAddress
FROM      Inserted I
      JOIN Deleted D ON I.UserID = D.UserID
WHERE      D.UserEmailAddress <> I.UserEmailAddress

-- etc.. for each table
--------------------------------------

As you can see I will have to write a seperate Insert/Where clause for each column I want to audit.. which is in the region of 60, which I can do. However I'm thinking there must be an easier way and less resource intensive to achieve this via conditional code.

Can anyone give me a jumpstart?

Many thanks,

Neil
0
Comment
Question by:nellster
  • 2
  • 2
4 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 19633562
try this

if update (UserBadgeName)
INSERT INTO AuditTable (UserID, TableName, ColumnName, LastModifiedDate, LastModifiedUser, OldValue, NewValue)
SELECT      I.UserID, 'Users', 'Badge Name', I.LastModifiedDate, I.LastModifiedUser, D.UserBadgeName, I.UserBadgeName
FROM      Inserted I
      JOIN Deleted D ON I.UserID = D.UserID

-- Audit UserEmailAddress Changes, if any
if update (UserEmailAddress)
INSERT INTO AuditTable (UserID,TableName, ColumnName, LastModifiedDate, LastModifiedUser, OldValue, NewValue)
SELECT      I.UserID, 'Users', 'User EmailAddress', I.LastModifiedDate, I.LastModifiedUser, D.UserEmailAddress, I.UserEmailAddress
FROM      Inserted I
      JOIN Deleted D ON I.UserID = D.UserID
0
 

Author Comment

by:nellster
ID: 19653592
Hi Imran.. thanks for the reply. Forgive my stupidity.. but how does your code differ from mine?

Cheers,

Nellster
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 750 total points
ID: 19675025
Ok well i have added if update clause which means if that particular column is updated only then it will execute the statement instead of checking the data completely in deleted and inserted virtual tables.
0
 

Author Comment

by:nellster
ID: 19690293
Thanks Imran.. I get it!!

Nellster
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

809 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