DDL Triggers

DDL stands for Data definition language.  Nothing new until now, you might know DDL as the alter, create and drop statements from SQL.

However, in Microsoft SQL Server 2005 it is possible to add triggers for these statements. 

These kind of triggers allow you to audit these potentially dangerous statements:

CREATE TRIGGER safety
ON DATABASE
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE
AS
   PRINT 'You must disable Trigger "safety" to drop, create or alter tables!'
   ROLLBACK ;

DDL triggers fire only in response to DDL events specified by Transact-SQL DDL syntax. System stored procedures that perform DDL-like operations are not supported.

To see a list of the triggers created (in the system catalog), you can use this statement

SELECT name,te.type_desc,te.type
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents1','ddlTestEvents2')
ORDER BY name,te.type_desc

Where ddlTestEvents1 and ddlTestEvents2 are of course the names of the events.

If you need to create for the 3 statements the same trigger, you can use

FOR DDL_TABLE_EVENTS

instead of all the individual events.  You will observe the same effect in the triggers catalog.