Someone wanted to know when and by who a certain table was dropped, I told the person that you can do this with a DDL trigger.Wouldn’t it be nice if you could track exactly all the DDL statements that were executed on a table in your database? Well, you can by using DDL Triggers, DDL Triggers were added in SQL Server 2005.

DDL triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations. A DDL trigger can be created on the database level or on the server level. In this post I will create a database level DDL trigger that will listen for the ALTER TABLE command.

First I will create a sample database

CREATE DATABASE TestTrigger
GO

USE TestTrigger
GO

Now I will create a table which will hold the DDL statement, the time and the login of the person who executed the statement

CREATE TABLE TriggerLog(DDL VARCHAR(300), ExecutedBy VARCHAR(100), EventDate datetime)
GO

Here is what my DDL trigger will look like, more information about DDL triggers can be found here: http://msdn.microsoft.com/en-us/library/ms189799.aspx

CREATE TRIGGER trALterTable 
ON DATABASE -- A DB level trigger
FOR ALTER_TABLE --Event we want to capture
AS 
  INSERT TriggerLog
  SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'), 
		COALESCE(SUSER_SNAME(),USER_NAME()), 
		GETDATE();
GO

The code in the trigger should be pretty simple to follow. The line _EVENTDATA().value(‘(/EVENTINSTANCE/TSQLCommand/CommandText)1’,‘nvarchar(max)’) is grabbing the DDL statement, more about EVENTDATA() can be found here: http://msdn.microsoft.com/en-us/library/ms173781.aspx

Next up is the test table that we will use to play around with

CREATE TABLE test(id INT)

The following block of code will add a column, change the data type of the column and will finally drop the column

ALTER TABLE test
ADD SomeDate date
GO

ALTER TABLE test
ALTER COLUMN SomeDate datetime2
GO


ALTER TABLE test
DROP COLUMN SomeDate 
GO

Now let’s see what we have in our log table

SELECT * FROM TriggerLog
order by EventDate

Here is the output

As you can see we have all the DDL statements captured in the table, the time it happened and the person who did it.

Let’s just drop and recreate the table

drop table Test
GO

Now create the table again

CREATE TABLE test(id INT)
GO

If you now execute this query, you will get back pretty much all the DDL statements that we executed before

SELECT DDL + 'GO'
FROM TriggerLog
ORDER BY EventDate

Here is what it looks like if you copied the results and pasted them into a query window.

ALTER TABLE test
ADD SomeDate date
GO
ALTER TABLE test
ALTER COLUMN SomeDate datetime2
GO
ALTER TABLE test
DROP COLUMN SomeDate 
GO

This was just a small example of how a DDL trigger works, A DDL trigger enables you to also not allow ALTER TABLE statements during business hours or for certain user even though they are db owner.

To see all the events that DDL triggers can listen for you can use the sys.trigger_event_types Object Catalog View

select type_name from sys.trigger_event_types 

Here is a partial result set

——————–

CREATE_TABLE

ALTER_TABLE

DROP_TABLE

CREATE_INDEX

ALTER_INDEX

DROP_INDEX

CREATE_STATISTICS

UPDATE_STATISTICS

DROP_STATISTICS

CREATE_SYNONYM

DROP_SYNONYM

CREATE_VIEW

ALTER_VIEW

DROP_VIEW

CREATE_PROCEDURE

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum