Creating a DDL Trigger that Audits Database-Level Events : Trigger on database « Trigger « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Trigger
3. Trigger on database

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>