Mega Code Archive

 

Categories / MSSQL Tutorial / Trigger

 

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>