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?
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
Now you have an audit trail that is easy to query and analyze. You can also experiment with different combinations of inserts and updates.
- SQL Server Trigger After Update for a Specific Value
- Forcing Trigger Firing Order in SQL Server
- Prevent accidental update or delete commands of all rows in a SQL Server table
- Auditing when Triggers are Disabled or Enabled for SQL Server
Last Update: 10/7/2015
About the author
View all my tips