I have a DDL Trigger in SQL Server 2012 as:
CREATE TRIGGER [AuditProcChanges]
ON DATABASE FOR
CREATE_TABLE,ALTER_TABLE,DROP_TABLE
AS
DECLARE @message_body XML
SET @message_body = EVENTDATA()
BEGIN
BEGIN TRANSACTION
INSERT INTO MyChangeLog(vcEventType, dtEventTime, vcServerName, vcLoginName
, vcUserName, vcObjectName, vcObjectType, vcSQL)
SELECT @message_body.value('(/EVENT_INSTANCE/EventType)[1]',
'varchar(128)') AS EventType,
GETUTCDATE() AS PostTime,
@message_body.value('(/EVENT_INSTANCE/ServerName)[1]',
'varchar(128)') AS ServerName,
@message_body.value('(/EVENT_INSTANCE/LoginName)[1]',
'varchar(128)') AS LoginName,
@message_body.value('(/EVENT_INSTANCE/UserName)[1]',
'varchar(128)') AS UserName,
@message_body.value('(/EVENT_INSTANCE/ObjectName)[1]',
'varchar(128)') AS ObjectName,
@message_body.value('(/EVENT_INSTANCE/ObjectType)[1]',
'varchar(128)') AS ObjectType,
@message_body.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(max)') AS TSQLCommand
COMMIT TRANSACTION
END
But when I'm altering the table from design view (right click and Design), the events that are getting logged are only of creation of temp table and dropping of main table. Create_Table event is not getting captured.
Can't we capture the log events when we alter a table from design view. Please help.
create_table
event is not captured, alter the body of your trigger to have only aRollback transaction
and then try to create a new table.