How to Create a SQL Server Audit Trigger

Native Auditing

Netwrix Auditor for SQL Server

Steps

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

Create Audit trigger by running the following script: (please, note that we should join tables by 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

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

Report sample:

Sample Report - SQL Server Audit Trigger

Run Netwrix Auditor → Select “Reports” → choose “SQL Server” → Select "All SQL Server Data Changes" report → Click "View".
Subscribe to this report and receive it via e-mail or have it delivered to a specified shared folder according to the schedule you set.

Report sample:

Go Beyond Creating an SQL Server Audit Trigger for INSERT or UPDATE Tables to Abate Threats to Your Critical Data

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.

There’s an easier and better way to keep an eye on changes to tables, schemas and more than by manually creating SQL Server database audit triggers or DDL triggers and painstakingly crawling through cryptic data. Netwrix Auditor for SQL Server maximizes visibility into what’s happening across your SQL servers, including what changes were made to your sensitive information, who made them and when. Forget about setting up multiple SQL Server audit table triggers — the solution collects and consolidates changes from all your tables and databases automatically. The prebuilt reports enable you to easily spot changes and the Interactive Search feature makes it easy to investigate aberrant changes, so you can proactively catch data threats before it’s too late.

Previous How-to

How to Detect Who Has Access to What Data on Windows File Servers

Next How-to

How to Create a SQL Server Logon Trigger