Simple Database Change Log in SQL Server 2008

Another pesky bug came out just recently. After scrutinizing the logs I discovered that it was caused by a parameter mismatch between the stored procedure and our our service. Someone modified the procedure, probably for testing purposes. There's no way for us to find out who actually did it because the database objects are not under any source control. If it were a SQL Server database, one thing that the DBA could have done was implement a basic change logging mechanism using Data Definition Language (DDL) Trigger.

Data Definition Language (DDL) Trigger is a code block that is executed by SQL Server after a certain event concerning some specific objects. The complete list of events is found here. DDL trigger was was first introduced in SQL Server 2005 as part of the SQL-CLR feature but I wasn't able to delve much deeper into it because I was pretty much concentrated with UDT then. With 2008, it was ported to SQL Server and that means it can now be created using T-SQL. The fact that I can create DDL trigger without compiling from CLR assembly made the new future very enticing. The benefit I ultimately get from DDL trigger made me hate myself for not learning it when it first came out 4 years ago. It provided me with a simple way of tracking changes in my database. If you are part of team where database security is very lenient (everyone is an admin), then this tool is indispensable.

My database change log is nothing more than a table populated by a generic DDL trigger. I say generic because it takes care of all the events from the most volatile database objects like table, procedure, view, trigger and function. Change among these could surely wreck havoc to the objects in the upper tier. The change log table for storing the changes information is shown below:

The columns are mirrored from the elements found in the schama returned by EventData(). If SQL trigger is to CLR event handler, then EventData() is the EventArgs. The schema of EventData varies depending on the object and the event. You can check the complete schema here. The columns are the ones common to all the events of the volatile object I just mentioned. Take note that EventData() is a context-sensitive function. It can only be utilized inside the body of a DDL trigger.

The trigger tracks all the pertinent events and inserts the data from EventData(). The look might be a novelty to anyone who haven't worked with XML data type.

 

The value() is a SQL XML data type method for navigating the nodes. It uses XPath syntax for the first argument. The second argument is the data type to which the result is converted to. Don't concern yourself too much with the syntax. This is all you really need to know about XML data type as far as DDL trigger is concerned.

After you catalogue the trigger, it appears in the Database Triggers node under your database as shown below:

That's it! Try creating, updating and dropping the objects monitored by the trigger and see the rows inserted. Oh, did I mention that the entire command is also available to you? Just check TSQLCommand column for that.

Be aware though that there are temporary objects that SQL Server generates in some instances like when you alter your table. These temporary objects can also fire the trigger. It's easy to spot them because they have a consistent name format Tmp_<objectName>; for example Tmp_Employee.

One thing you probably want to happen is for your future databases to have this simple change logging mechanism. If that's the case, then do yourself a favor and create the two objects in the model database.

To the SQL Server 2005 folks, hey I'm not leaving you out. We'll tackle 2005 next time. ;)