Create Trigger To Log DDL Events

CREATE TRIGGER [trgLogDDLEvent] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
set nocount on
DECLARE @data XML
SET @data = EVENTDATA()
IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
<> 'CREATE_STATISTICS'
INSERT INTO DdlLog..ChangeLog
(
EventType,
ObjectName,
ObjectType,
tsql
)
VALUES (
@data.value('(/EVENT_INSTANCE/EventType)[1]',
'varchar(100)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]',
'varchar(100)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]',
'varchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
'varchar(max)')
) ;


ENABLE TRIGGER [trgLogDDLEvent] ON DATABASE