Geek fun with DDL Triggers

This positing first appeared in an earlier blog that I have now replaced with this current opus. It's a bit more technical that most of what I am putting in this blog, but I'm still a database geek at heart and I wanted to move it over before I wrapped up the earlier blog. I hope no-one takes offence at this bit of self plagiarism

With all the big changes in SQL Server 2005 I've been focusing on some small but significant changes. The first of these is the Data Definition Language (DDL) triggers.

In a nutshell, SQL Server 2005 will allow you to set a trigger at the database level for a myriad of DDL actions (consult your friendly neighborhood BooksOnline for the full list).

I've chosen an particular application of this functionality here, one that solves a problem I had with earlier SQL versions -- table management.

The problem is like this: suppose you, as DBA, notice that a table has been deleted. You go into the SQL Server logs but find no reference to the dropped table. Nor can you find out who dropped it and when.

Now you can.

In a test database, I created a log table in my test DB that looked like this:

CREATE TABLE [dbo].[DDL_Log]
( [id] [int] IDENTITY(1,1) NOT NULL,
[DDL_Event] [nvarchar](100) NOT NULL,
[TSQL_code] [nvarchar](2000) NOT NULL,
[ExecutedBy] [nvarchar](100) NOT NULL DEFAULT current_user,
[Action_date] [datetime] NULL DEFAULT (getdate()))
ON [PRIMARY];

I then created a DDL trigger on the database using the following syntax:

CREATE TRIGGER [log_table_actions]
ON DATABASE FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
BEGIN

DECLARE @data XMLSET @data = EVENTDATA()
INSERT dbo.ddl_log (DDL_Event, TSQL, ExecutedBy, Action_Date)
VALUES (@data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'),@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar (2000)'), CONVERT(nvarchar(100), (CURRENT_USER)), GETDATE())
End;

A few thing will jump out immediately. The first is that the trigger is created "ON DATABASE" rather than on a table and secondly, that the trigger is create on all CREATE, ALTER and DROP TABLE statements. Finally it uses the eventdata() function to query information on the DDL statement that called the trigger.

Please note that this function returns XML, so you'll need to capture the output inside and XML typed variable, and you need to query elements inside the element to get the data. In this case, I've returned the event type and the full TSQL code for the DDL statement.I'm still looking for the full schema for the eventdata() xml document. But that will be a post for another day.