• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

AuditTrail trigger on multiple columns / SQL condition loop?

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
nellster
Asked:
nellster
  • 2
  • 2
1 Solution
 
imran_fastCommented:
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
 
nellsterAuthor Commented:
Hi Imran.. thanks for the reply. Forgive my stupidity.. but how does your code differ from mine?

Cheers,

Nellster
0
 
imran_fastCommented:
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
 
nellsterAuthor Commented:
Thanks Imran.. I get it!!

Nellster
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now