Sql-server – DDL Trigger not working as expected SQL Server 2012

ddl-triggersql server

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.

Best Answer

Your trigger has no problem at all (the only question I have is why do you open another transaction inside your trigger if all you do is 1 SELECT only?)

When you alter a table in such a way it has to be recreated (for example adding a column not at the end of the existing columns), the actions server takes are following (simplified):

  • create a new table called dbo.Tmp_old_tbl having the new structure (note, it's not temporary table that lives in tempdb, it's just a new table residing in your db)
  • insert all the data from your old table into the new one
  • EXECUTE sp_rename N'dbo.Tmp_old_tbl', N'old_tbl', 'OBJECT'

So that "temp table" that you saw created is in fact your new table, renamed from Tmp to your old_tbl

Related Question