The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

«
»

Auditing T-SQL Script to find out who made changes to data in a table

Posted by Sudarshan Narasimhan on June 1, 2012


Having worked with multiple customers and different application where at times the data in the table has gotten changed and we need to find out when/where/who made the changes, I used the below T-SQL code to setup a trigger on the table and track the changes into an auditing table. There are obviously other ways to do this like SQL Profiler, XEvents etc., but I find using T-SQL more simpler and lesser overhead. So, sharing the script for others to use.

-- Note: This trigger tracks Updates and Deletes happening on a table. 
-- Please delete this trigger once the source has been identified and corrective actions have been taken.

-- 1. Creating the audit table to store information on Update/Delete
CREATE TABLE AuditTable
(
AuditID [int] IDENTITY(1,1) NOT NULL,
Timestamp datetime not null CONSTRAINT AuditTable_Timestamp DEFAULT (getdate()),
OperationType char(1),
OperationDate datetime DEFAULT (GetDate()), 
PrimaryKeyValue varchar(1000), 
OldColValue varchar(200), 
NewColValue varchar(200), 
UserName varchar(128),
AppName varchar(128),
ClientName varchar(128)
)
go

--2. Creating  the audit trigger
-- Replace PrimaryKeyValue with the PK Column Name
-- Replace NewColValue with the column name in the IF BLOCK
-- Replace OldColValue with the column name in the final SELECT statement
-- Replace TBLNAME with the name of your table which you want to track the changes for.

Create trigger TBLNAME_Audit on TBLNAME for update, delete
AS
declare @OperationType char(1),
@OperationDate datetime,
@NewColValue varchar(200),
@OldColValue varchar(200),
@UserName varchar(128),
@AppName varchar(128),
@ClientName varchar(128)

select @UserName = system_user
select @OperationDate = CURRENT_TIMESTAMP
select @ClientName = HOST_NAME()
select @AppName = APP_NAME()

if exists (select * from deleted)
      if exists (select * from inserted)
      begin
            select @OperationType = 'U'
            select @NewColValue = NewColValue from inserted
      end
      else
      begin
            select @OperationType = 'D'
            select @NewColValue = null
      end
      
Insert AuditTable (OperationType, OperationDate, PrimaryKeyValue, OldColValue, NewColValue, UserName, AppName, ClientName)
select @OperationType, @OperationDate, PrimaryKeyValue, OldColValue, @NewColValue, @UserName, @AppName, @ClientName
from deleted
go

--3. Query the audit table once the values in the base table has changed
select * from AuditTable

This entry was posted on June 1, 2012 at 9:59 PM and is filed under Auditing, Security, T-SQL. Tagged: , , , , , , . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

5 Responses to “Auditing T-SQL Script to find out who made changes to data in a table”

  1. siva said

    June 4, 2012 at 10:06 PM

    Hi Sudarshan,

    Some time this code was really help ful…

    Nice article

    Tx

    Reply

  2. Ameya Thakur said

    December 10, 2013 at 9:14 AM

    Hello Sudarshan,

    Thanks for this code. I am trying to use to code to create a Audit table on one on my Transactional Table in SQL 2008. I have modified the script as per your instructions, but looks like I am making some mistakes in the modification . I am receiving this error on this code :

    Error : Msg 156, Level 15, State 1, Procedure TBLNAME_Audit_1, Line 41
    Incorrect syntax near the keyword ‘select’.

    This error is pointing to line : select @NewColValue = select NewcolValue from inserted

    Can you please suggest, what I must be missing ? What is the NewColValue I must be entering here? Any hint on this will be very helpful

    – Amey Thakur

    Reply

  3. epo said

    January 3, 2014 at 6:17 PM

    I have the same issue as Ameya Thakur’s comment. any suggestions?
    Thanks. great article

    Reply

    • Sudarshan Narasimhan said

      February 22, 2016 at 8:03 AM

      My bad. Change the following line as follows and it will work
      Original: select @NewColValue = select NewColValue from inserted
      Change To: select @NewColValue = NewColValue from inserted

      I’ve modified the script in the article to reflect this change. Cheers.

      Reply

Leave a comment

«
»