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