Using DDL Triggers in SQL Server 2005 to Capture Schema Changes

By Chris Rock on 13 August 2007 | Tags: Triggers


This article shows how to use DDL triggers to capture schema changes. This solutions works a little differently than most DDL triggers examples. This script captures the old object that was replaced.

A while back I was assigned to the role of backup DBA. I referred to myself as the pseudo-DBA. I liked it because it was a challenge to me. Something new is always good for a geek. I learned a lot during my tenure which ended when we hired a new DBA. Looking back the one task I feared most during that time was updates from the other developers. Not because they gave me crappy code (well, some of them did) but because of the tedious nature of the system we had in place.

We had a system setup to send all change requests through me. This was in place as to not disturb the real DBA who responsibilities were to keep the enterprise running. More than a few times a day I'd get an email with change request. No big deal right? Well it wasn't a big deal to actually deploy the changes. The problem was keeping a backup of the old object in a change log. This required me to script out the old object and then save that data to a table. I had a little ASP app setup to paste the text of the script, the name of the database and the name of the object into the form. When I clicked the "Save" button the data was inserted into the change log table. Even with the little app setup I'd sometimes forget, be in a rush, or for some other stupid reason I wouldn't back up the old object. That led to trouble.

As I said before, this was a tedious and sometimes annoying part of the job. I tried a few source safe systems but was always disappointed with their performance or they required you to use their environment for making changes. There wasn't any integration into query analyzer. I eventually gave up and just accepted that this was the way it was going to be.

This is where you are lucky. I think I found a way for you, the DBA, psuedo-DBA, forced DBA, etc to get the same type of change log system with just a little setup.

There isn't a rotisserie involved but with this system, you can "set it and forget it!".

FYI, Those rotisseries rock!

DDL Triggers

SQL Server 2005 introduced a new trigger called the DDL Trigger. For you newbies, DDL means "Data Definition Language". (e.g. create table, alter table, create proc, etc.) What's great about these new triggers is if they are setup on the database/server, you are able to capture the DDL statement and automatically log it to a change log. You have no idea how much easier that is than doing it manually. Also, the laziness in all of us gets a little boost.

Trigger Scope

The DDL Triggers have two scope levels; database and server. The server-scoped statements that can be captured are:

ALTER_AUTHORIZATION_SERVER

CREATE_DATABASE

ALTER_DATABASE

DROP_DATABASE

CREATE_ENDPOINT

DROP_ENDPOINT

CREATE_LOGIN

ALTER_LOGIN

DROP_LOGIN

GRANT_SERVER

DENY_SERVER

REVOKE_SERVER

The database-scoped statements are:

CREATE_APPLICATION_ROLE

ALTER_APPLICATION_ROLE

DROP_APPLICATION_ROLE

CREATE_ASSEMBLY

ALTER_ASSEMBLY

DROP_ASSEMBLY

ALTER_AUTHORIZATION_DATABASE

CREATE_CERTIFICATE

ALTER_CERTIFICATE

 DROP_CERTIFICATE

CREATE_CONTRACT

DROP_CONTRACT

GRANT_DATABASE

DENY_DATABASE

REVOKE_DATABASE

CREATE_EVENT_NOTIFICATION

DROP_EVENT_NOTIFICATION

CREATE_FUNCTION

ALTER_FUNCTION

DROP_FUNCTION

CREATE_INDEX

ALTER_INDEX

DROP_INDEX

CREATE_MESSAGE_TYPE

ALTER_MESSAGE_TYPE

DROP_MESSAGE_TYPE

CREATE_PARTITION_FUNCTION

ALTER_PARTITION_FUNCTION

DROP_PARTITION_FUNCTION

CREATE_PARTITION_SCHEME

ALTER_PARTITION_SCHEME

DROP_PARTITION_SCHEME

CREATE_PROCEDURE

ALTER_PROCEDURE

DROP_PROCEDURE

CREATE_QUEUE

ALTER_QUEUE

DROP_QUEUE

CREATE_REMOTE_SERVICE_BINDING

ALTER_REMOTE_SERVICE_BINDING

DROP_REMOTE_SERVICE_BINDING

CREATE_ROLE

ALTER_ROLE

DROP_ROLE

CREATE_ROUTE

ALTER_ROUTE

DROP_ROUTE

CREATE_SCHEMA

ALTER_SCHEMA

DROP_SCHEMA

CREATE_SERVICE

ALTER_SERVICE

DROP_SERVICE

CREATE_STATISTICS

DROP_STATISTICS

UPDATE_STATISTICS

CREATE_SYNONYM

DROP_SYNONYM

CREATE_TABLE

ALTER_TABLE

DROP_TABLE

CREATE_TRIGGER

ALTER_TRIGGER

DROP_TRIGGER

CREATE_TYPE

DROP_TYPE

CREATE_USER

ALTER_USER

DROP_USER

CREATE_VIEW

ALTER_VIEW

DROP_VIEW

 CREATE_XML_SCHEMA_COLLECTION

 ALTER_XML_SCHEMA_COLLECTION

DROP_XML_SCHEMA_COLLECTION

Yeah, that's a lot of events that can be captured. For our purposes we're just going to focus on a select group of events that surround tables, stored procedures and functions.

Creating a DDL Trigger

It's pretty simple to create a DDL trigger. It's very similar to a table trigger:

CREATE TRIGGER [name of trigger]
ON [scope (database|server)]
FOR [event]
    As
--Trigger definition here…

Here is an example of a real DDL trigger:

create trigger backup_procs
on database
for create_procedure, alter_procedure, drop_procedure
as

set nocount on 

declare @data xml
set @data = EVENTDATA()

insert into dbo.eventslog(eventtype, objectname, objecttype, sqlcommand, username)
values(@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
go

The script above will create a DDL trigger called backup_procs. It will insert a row into the eventslog table when a proc is created, altered or dropped. You'll see a more in depth explanation of the DDL trigger used in the change log system in a few minutes.

What's that EVENTDATA() thingy?

EVENTDATA is where we're going to get our data for the change log system. It's basically an XML datatype that looks like this:

<EVENT_INSTANCE>
    <EventType>event </EventType>

    <PostTime>date-time</PostTime>
    <SPID>spid</SPID>
    <ServerName>name </ServerName>

    <LoginName>login </LoginName>
    <UserName>name</UserName>
    <DatabaseName>name</DatabaseName>

    <SchemaName>name</SchemaName>
    <ObjectName>name</ObjectName>
    <ObjectType>type</ObjectType>

    <TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>

The XML contains all of the information you'll need to create an entry into your changelog table.

The change log system

Ok. Now we're getting to the actual system we'll use to backup our objects. It's pretty basic now but you can expand on it greatly by adding extra columns or events to the trigger.

The change log system is using a database scoped trigger. That means every database you're going to use it on is going to need the trigger defined there. It's up to you whether you want to put the changelog table in each database or use a central database. I'm going to use a central database model, therefore all of the my inserts will be qualified with eventslogdb.dbo.changelog. We're going to capture 9 events: create, alter and drop table; create, alter and drop procedure; create, alter and drop function.

The changelog table will be defined as:

CREATE TABLE [dbo].[ChangeLog](
	[LogId] [int] IDENTITY(1,1) NOT NULL,
	[DatabaseName] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[EventType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ObjectType] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SqlCommand] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate]  DEFAULT (getdate()),
	[LoginName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

Our DDL trigger is defined as:

create trigger backup_objects
on database
for create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function
as

set nocount on

declare @data xml
set @data = EVENTDATA()

insert into eventslogdb.dbo.changelog(databasename, eventtype, 
    objectname, objecttype, sqlcommand, loginname)
values(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)

go

Just for the sake of the example I put this trigger in the same database where my changelog table resides.

Let's breakdown that trigger.

create trigger backup_objects
on database

We're creating a trigger named backup_objects that is scoped for the database.

for create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function

The trigger will fire when a create procedure, alter procedure, drop procedure, create table, alter table, drop table, create function, alter function or drop function statement is executed.

set nocount on

If nocount is set to off then you'll get a row count when executing any DDL statements for the above events. No big deal, but I don't getting a message like "1 row(s) affected" when altering a proc.

declare @data xml
set @data = EVENTDATA()

Remember that EVENTDATA will get the details of the executed DDL statement. We're simply assigning it to a local xml variable called @data.

insert into eventslogdb.dbo.changelog(databasename, eventtype, 
    objectname, objecttype, sqlcommand, loginname)
values(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)

Insert the event data into our changelog table. @data.value is calling the xquery function "value". The value function is defined as "value(Xquery, SQLType)".

@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)') is going to return the name of the database where the DDL trigger is being fired as a varchar(256). The rest of the @data.value calls are similar to this one. You should be able to figure out what each of those functions return.

I used the LoginName element from EVENTDATA() because it returns the login associated with the SID (security identification number). It's similar to using suser_name(). If you use the UserName element from the EVENTDATA function you will not get the true identification of the user executing the statement. If it's always you making the change, then this is not a problem. However, if you have more than one DBA (or... gasp... developers!) making changes, it's a good idea to keep track of who is making the change.

Causing the triggers to fire

If you've paid attention you now know that the DDL trigger we created will fire when we create, alter or drop a table, proc or function. I'm going to create this proc in my database:
use eventslogdb
go

CREATE proc [dbo].[usp_GetDBVersion]
as

select @@version

Yes, it's simplistic but it works. This will fire the backup_objects trigger and insert a row into the changelog table. Now let's take a look at the row in the changelog table:

SELECT [LogId]
      ,[DatabaseName]
      ,[EventType]
      ,[ObjectName]
      ,[ObjectType]
      ,[SqlCommand]
      ,[EventDate]
      ,[LoginName]
FROM [EventsLogDb].[dbo].[ChangeLog]

Results:

LogId       DatabaseName EventType        ObjectName         ObjectType     
----------- ------------ ---------------- ------------------ ------------- 
1           EventsLogDB  CREATE_PROCEDURE usp_GetDBVersion   PROCEDURE      

                                                                            
SqlCommand                            EventDate                LoginName
------------------------------------- ------------------------ --------------
CREATE proc [dbo].[usp_GetDBVersion]   2007-07-23 20:14:29.780  ROCK-XP\chris
as
select @@version                                                                            

The data in the table is pretty self-explanitory. I'm sure you can see how helpful this would be if you need to track down a change made to one of your objects.

One last bit of information. If you alter or drop the changelog table and do not update/drop the DDL trigger to reflect the change, any future DDL statements could fail because of this. It's happened to me and I hope it doesn't happen to you!

So that's it. It's a simple yet powerful way to keep track of changes to your database. Remember, just "set it and forget it". My apologies to Mr. Popeil.


- Advertisement -