Fun with DDL Triggers

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 BOL for the full list).

I've chosen an particular application of this fuctionality 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] [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 XML
SET @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.