Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Create a Simple SQL Server Trigger to Build an Audit Trail


By:   |   Read Comments (2)   |   Related Tips: More > Triggers

Problem

I have a SQL Server table in which I need to see who changed what and when. Is there a simple way to create an audit trail in SQL Server to meet these needs?

Solution

In this tip, we show you how to write T-SQL statements that will create a trigger to populate an audit table to store changes to existing records and record when new records are added to the table. We are using SQL Server 2016 CTP 2.0, but this could be done in any version of SQL Server.

Create Example SQL Server Tables

In the top portion of the image below we see where we create our example data table named tblOrders with a primary key column named OrderID, a date column named OrderApprovalDateTime and a varchar column named OrderStatus.

In the bottom portion of the image below we see where we create our table named tblOrdersAudit that will record changes to the data in the table tblOrders. The audit table has its own primary key column named OrderAuditID, all of the columns and their data types from tblOrders, plus columns named UpdatedBy and UpdatedOn. The data type for the UpdatedBy column is nvarchar(128) which is the data type returned by the SUSER_SNAME() function that will be used to get the Login name for the principal making the changes.

Create the data table and the audit table

Create Example SQL Server Audit Trigger

In this example, we want our trigger to insert a row into the tblOrdersAudit table any time a record is updated or inserted in tblOrders, essentially taking a snapshot of the new or changed record along with which principal affected the record and when it happened. The T-SQL for creating this trigger is shown below.

Successfully create the trigger

Test SQL Server Audit Trigger for Inserts

To test the trigger for its behavior on inserting new records, we populate the table with three test records with the OrderApprovalDateTime set to NULL and the OrderStatus set to "Pending". We then select from both tables and inspect the results as shown below. We can see that the new records are in the audit table.

Successfully test the trigger on INSERT

Test SQL Server Audit Trigger for Updates

Next, we will test the trigger's behavior on an update. We will set the order status to approved and set the order approval date for the first record (OrderID=1). When we query both tables we can see how the changes to OrderID=1 are recorded in tblOrdersAudit.

Successfully test the trigger on UPDATE of the first record

Let's test again, but this time let's update the record where OrderID=2. When we query both tables we can see how the changes to OrderID=2 are recorded in tblOrdersAudit.

Successfully test again the trigger on UPDATE of the second record

This time let's update the OrderStatus to Cancelled for the record where OrderID=1 . We can see in tblOrdersAudit where the order was approved and then cancelled.

Test the trigger when the order status is set to Cancelled

Sample SQL Server Audit Trigger Code

use MSSQLTips
go

create table tblOrders
(
  OrderID integer Identity(1,1) primary key,
  OrderApprovalDateTime datetime,
  OrderStatus varchar(20)
)

create table tblOrdersAudit
(
  OrderAuditID integer Identity(1,1) primary key,
  OrderID integer,
  OrderApprovalDateTime datetime,
  OrderStatus varchar(20),
  UpdatedBy nvarchar(128),
  UpdatedOn datetime
)
go
  
create trigger tblTriggerAuditRecord on tblOrders
after update, insert
as
begin
  insert into tblOrdersAudit 
  (OrderID, OrderApprovalDateTime, OrderStatus, UpdatedBy, UpdatedOn )
  select i.OrderID, i.OrderApprovalDateTime, i.OrderStatus, SUSER_SNAME(), getdate() 
  from  tblOrders t 
  inner join inserted i on t.OrderID=i.OrderID 
end
go

insert into tblOrders values (NULL, 'Pending')
insert into tblOrders values (NULL, 'Pending')
insert into tblOrders values (NULL, 'Pending')
go

select * from tblOrders
select * from tblOrdersAudit

update tblOrders 
set OrderStatus='Approved', 
OrderApprovalDateTime=getdate()  
where OrderID=1
go

select * from tblOrders
select * from tblOrdersAudit order by OrderID, OrderAuditID
go

update tblOrders 
set OrderStatus='Approved', 
OrderApprovalDateTime=getdate()  
where OrderID=2

go

select * from tblOrders
select * from tblOrdersAudit order by OrderID, OrderAuditID
go

update tblOrders 
set OrderStatus='Cancelled'
where OrderID=1
go

select * from tblOrders
select * from tblOrdersAudit order by OrderID, OrderAuditID
go
Next Steps

Now you have an audit trail that is easy to query and analyze. You can also experiment with different combinations of inserts and updates.

Also, check out more tips on triggers in SQL Server on MSSQLTips.com.



Last Update:






About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Wednesday, June 01, 2016 - 6:51:25 AM - Steve Tipton Back To Top

"I do not understand how the trigger can work on updates as well as it works on inserts since you only state the "inserted" in the join clause ..."

 

In response to Jakub, I had a similar confusion and located this, which explains the usage of automatically-created tables to store inserts (this accounts for updates) and deletes:

https://msdn.microsoft.com/en-us/library/ms191300(v=sql.130).aspx

I hope that helps clear up confusion!

Steve


Monday, April 25, 2016 - 7:34:50 AM - Jakub Svoboda Back To Top

Hey Dallas,

Thanks for a nice guide, however I do not understand one part of the trigger. Maybe I just overlooked something. Can you help me out? I do not understand how the trigger can work on updates as well as it works on inserts since you only state the "inserted" in the join clause.

See here:

create trigger tblTriggerAuditRecord on tblOrders
after update, insert
as
begin
  insert into tblOrdersAudit 
  (OrderID, OrderApprovalDateTime, OrderStatus, UpdatedBy, UpdatedOn )
  select i.OrderID, i.OrderApprovalDateTime, i.OrderStatus, SUSER_SNAME(), getdate() 
  from  tblOrders t 
  inner join inserted i on t.OrderID=i.OrderID 
end
go

From my understanding, the inserted in the join clause is saying insert to the audit table ONLY the rows that were inserted to the main table. So, should not I just specify somewhere that when there is update on that table, the trigger should do the insert as well?

Hope my concern is clear, thanks a lot for help!

Jakub


Learn more about SQL Server tools