Create a Simple SQL Server Trigger to Build an Audit Trail

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.

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.

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.

Resources

Community

Subscribe