Creating a DDL Trigger that Audits Database-Level Events

  CREATE TABLE MyAudit (EventData xml NOT NULL, AttemptDate datetime NOT NULL DEFAULT GETDATE(),
DBUser char(50) NOT NULL)
GO
CREATE TRIGGER db_trg
ON DATABASE
FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
AS
SET NOCOUNT ON
INSERT dbo.MyAudit 
(EventData, DBUser)
VALUES (EVENTDATA(), USER)
GO
CREATE NONCLUSTERED INDEX ni_MyAudit_DBUser ON
MyAudit(DBUser)
GO
SELECT EventData
FROM MyAudit
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

  drop table MyAudit
  GO