SQL Server/T-SQL Tutorial/Trigger/Trigger on database

CREATE TRIGGER prevent_drop_triggers ON DATABASE FOR DROP_TRIGGER

CREATE TRIGGER prevent_drop_triggers
       ON DATABASE FOR DROP_TRIGGER
       AS PRINT "You must disable "prevent_drop_triggers" to drop any trigger"
       ROLLBACK

Creating a DDL Trigger that Audits Database-Level Events

4>
5> CREATE TABLE MyAudit (EventData xml NOT NULL, AttemptDate datetime NOT NULL DEFAULT GETDATE(),
6> DBUser char(50) NOT NULL)
7> GO
1>
2> CREATE TRIGGER db_trg
3> ON DATABASE
4> FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
5> AS
6> SET NOCOUNT ON
7> INSERT dbo.MyAudit
8> (EventData, DBUser)
9> VALUES (EVENTDATA(), USER)
10> GO
1>
2> CREATE NONCLUSTERED INDEX ni_MyAudit_DBUser ON
3> MyAudit(DBUser)
4> GO
1>
2> SELECT EventData
3> FROM MyAudit
4> GO
EventData

------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
----------------
<EVENT_INSTANCE><EventType>CREATE_INDEX</EventType><PostTime>2007-10-20T00:37:42.357</PostTime><SPID>51</SPID><ServerNam
e>BCE67B1242DE45A\SQLEXPRESS</ServerName><LoginName>BCE67B1242DE45A\Administrator</LoginName><UserName>dbo</UserName><Da
tabaseName>maste
1>
2> drop table MyAudit
3> GO
1>