Share via

How to Create a SQL Server Audit Trigger

  • Article

Why It is Important

SQL Server is one of the most critical systems in the IT infrastructure, storing valuable data and serving as the back end for many business-critical applications. Because even a small inappropriate action to a table with sensitive data, such as an update or insert, can lead to disruption in data integrity, tracking changes to SQL Server tables must be a top priority for database administrators. Native tools can help you create SQL Server audit triggers — for example, you can create a trigger to track changes to a table, such as data insertion, update or deletion. However, keep in mind that in order to create any SQL Server audit trigger that logs changes to your audit trail, you need to be fluent in Transact-SQL. Plus, you will need to create similar but distinct triggers for each table you need to track in order to meet audit specifications. 

Native Auditing

  1. Create an audit table with columns equal to the audited database that to record the changes: for example, we need to audit table named Sales 1 with columns -  ContactID, DateAquired and ContactStatus.
create table  AuditTable1
(
AuditID integer  Identity(1,1) primary key,
ContactID integer,
DateAquired datetime,
ContactStatus varchar(20),
WhoChanged nvarchar(128),
WhenChanged datetime
) 
go
  1. Create Audit trigger by running the following script: (please note that you should join tables by a public key; in this case it is ContactID). 
create trigger  AuditTrigger1 on  Sales1
after update, insert
as
begin
insert into  AuditTable1
(ContactID, DateAquired, ContactStatus, WhoChanged, WhenChanged)
select i.ContactID, i.DateAquired, i.ContactStatus, SUSER_SNAME(), getdate()
from Sales1 t
inner join inserted i  on  t.ContactID=i.ContactID
end
go
  1. After we created a trigger, all changes will be recorded in the newly created table. You can view all changes in the table (AuditTable1) by executing this simple query:
Select * from AuditTable1 order by  WhenChanged
  1. Query output example:
    http://img.netwrix.com/howtos/sql_server_data_changes_native.png

Credits

Originally posted - https://www.netwrix.com/how_to_create_sql_audit_trigger.html