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

T-SQL
1
2
3
4
5
CREATE DATABASE TestTrigger
GO
 
USE TestTrigger
GO
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

T-SQL
1
2
CREATE TABLE TriggerLog(DDL VARCHAR(300), ExecutedBy VARCHAR(100), EventDate datetime)
GO
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

T-SQL
1
2
3
4
5
6
7
8
9
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
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(‘(/EVENT_INSTANCE/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

T-SQL
1
CREATE TABLE test(id INT)
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
ALTER TABLE test
ADD SomeDate date
GO
 
ALTER TABLE test
ALTER COLUMN SomeDate datetime2
GO
 
 
ALTER TABLE test
DROP COLUMN SomeDate 
GO
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

T-SQL
1
2
SELECT * FROM TriggerLog
order by EventDate
SELECT * FROM TriggerLog
order by EventDate

Here is the output

DDL ExecutedBy EventDate
ALTER TABLE test ADD SomeDate date Denis-PCDenis 2011-04-19 20:18:07.590
ALTER TABLE test ALTER COLUMN SomeDate datetime2 Denis-PCDenis 2011-04-19 20:18:09.900
ALTER TABLE test DROP COLUMN SomeDate Denis-PCDenis 2011-04-19 20:18:11.610

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

T-SQL
1
2
drop table Test
GO
drop table Test
GO

Now create the table again

T-SQL
1
2
CREATE TABLE test(id INT)
GO
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

T-SQL
1
2
3
SELECT DDL + 'GO'
FROM TriggerLog
ORDER BY EventDate
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.

T-SQL
1
2
3
4
5
6
7
8
9
ALTER TABLE test
ADD SomeDate date
GO
ALTER TABLE test
ALTER COLUMN SomeDate datetime2
GO
ALTER TABLE test
DROP COLUMN SomeDate 
GO
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