Various Triggers – Part 5

Another usage of Database Triggers is track Data Definition Language (DDL) changes that occur in a database. Today, I am going to enhance the AUTOS database script to add such auditing. This auditing is a very good practice when multiple people have syadmin rights to make such changes.

First, I am going to create a schema called Audit Database Tracking (ADT) to seperate the data tables from audit tracking tables.

Second, I am going to create a table to hold the keep track of the DDL changes that are occuring to the database.

Third, I am going to create a database trigger to capture the event data and add entries to the tracking table.

The EVENTDATA() function, only available for Server and Database DDL triggers, returns XML data is key to extracting information about the event. The FOR clause of the database trigger can narrow down what events you want to track. Last but not least, a database developer needs to be aware of other events such as replication that might not wanted to be tracked.

The image below shows what is captured in the audit table after a couple tables, triggers and stored procedures are created.

In summary, database triggers (AFTER) can be used to keep track of SCHEMA changes. Since the number of SCHEMA changes on a mature database are small, this is a great way to find out who did what when there is a production issue.

In SQL Server 2008, a built in Database Auditing mechanism was introduced. We will explore this feature in future articles.

Related posts

Leave a Comment