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

sql 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

sql
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

sql 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

sql
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

sql 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

sql
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

sql drop table Test GO


Now create the table again

sql
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

sql 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.

sql
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

sql 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