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