Various Triggers – Part 6

Triggers can be used to track Data Manipulation Language (DML) changes that occur in tables. Today, I am going to expand the AUTOS database script to add table auditing. Auditing is a very good practice when multiple people have rights to make changes to important semi-static data. This is not a good solution if the amount of data is large or number of table changes are huge.

First, I am going to leverage the newly built schema called Audit Database Tracking (ADT) to seperate data tables from audit tables. A key design decision is to have one single table to hold all table changes or have one audit table per data table that is being tracked.

Second, I am choosing to have a single table (CREATE TABLE) to keep track of the DML changes that are occuring to the database.

Third, I am going to create a dynamic TSQL script to create a trigger on each table that I want audited. The code uses a cursor to list all the tables that will be audited. For each table, a dynamic SQL statement is executed (EXEC) to make a trigger (CREATE TRIGGER) which is called for each INSERT, DELETE and UPDATE action.

The resulting data is stored in a xml field for later review. Additional information such as changed by id, change by host and changed datetime are stored in standard columns within the row. If mutliple actions occur against a table during the same transaction, one xml record is generated that contains multiple actions. Please see image below for xml data.

The inserted and deleted system tables are used to capture the in flight data. The code that is generated stores this data into our auditing table. The following table shows which in flight system tables are not empty per TSQL action.



Please see image below for triggers that are generated.

In summary, AFTER triggers can be used to keep track of DATA changes. If the number of DATA changes are small, this is a great solution to find out who did what when there is a production issue. In SQL Server 2008, a built in Change Data Tracking was introduced. We will explore this new feature in future articles.

Related posts

Leave a Comment