SQL Trigers

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 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.

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.

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.

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