DDL Triggers: Logging DB objects changes in SQL Server 2005

Posted on January 12, 2009. Filed under: Uncategorized | Tags: , , , , , , , |

We, SQL Server db coders, always lacked date modified filter option in management studio (or in enterprise manager). We always wanted that feature to be availabe in next Service Pack, or if not, in next version. But that never happened.

If you still don’t have solution to this – here are some good news: SQL Server 2005 comes with a new DDL triggers, using which we can track the changes in DB objects. And best thing about it is that it will take just few minutes to setup the log.

Here is how to do it:
1. First we create a log table, with necessary log fields as shown in figure 1.

2. Then we create a DDL-Trigger on the database (fig. 2) which gets triggered on any change to the database. We simply call EVENTDATA function, that returns a xml value that contains event details. We simply run a XQuery on the xml data to get necessary info, and save it to our log table.

DDL Trigger

fig-1: DDL Trigger

Trigger

fig-2: Trigger

Here is sample data in the table:

sample data

sample data

Also, if you are still stuck in some SQL Server 2000 database projects like me, you can still make use of the trigger by installing SQL Server 2005 and having your databases in 2000 mode (version 8).

In my team, we have created a simple DNN based UI, which allows us to list changes filtered by database, objects and date. Everybody simply creates an export file and puts it in svn at the end of the day. Then, all export files are combined in server database.

Advertisements

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: