Simple DDL Trigger for auditing at the database level

This simple DDL trigger will use the EVENTDATA() function to capture data at the database level when DDL statements are run

--Audit Table

CREATE TABLE audit_table
(
table_name  VARCHAR (200)
, [user] VARCHAR (200)
, date_time DATETIME
, activity VARCHAR (200)
)

--DDL Trigger (To be created on the database that needs to be audited)

CREATE TRIGGER DDL_Trigger
ON DATABASE
FOR ALTER_TABLE, DROP_TABLE, ALTER_PROCEDURE, REVOKE_DATABASE
AS
INSERT INTO audit_table
SELECT
 EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')
,SUSER_SNAME()
,GETDATE()
,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

Explanation:

I have enabled this trigger to capture only a few events such as ALTER, DROP and REVOKE but you can get the entire list of actions that can be captured in this link

http://msdn.microsoft.com/en-us/library/bb522542.aspx