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)','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
|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