3

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.

  • No errors or rollbacks? – Jacob H Jun 15 '17 at 13:30
  • Nopes. Infact I'm able to alter the table and see the changes on refresh. – aaru11 Jun 15 '17 at 13:31
  • To be sure that the create_table event is not captured, alter the body of your trigger to have only a Rollback transaction and then try to create a new table. – Sabin Bio Jun 15 '17 at 14:18
  • Create table event is captured when I'm creating using SQL statement. When altering the table from design view problem occurs. – aaru11 Jun 16 '17 at 5:40
5

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

| improve this answer | |
  • Thanks for the reply. After removing the transaction as well, no luck. I'm getting only the logs for Creation of Tmp table and Drop of my main table. Actually we are using the ChangeLog for creating release scripts for deployment. As after DROP TABLE , we are not getting any CREATE TABLE log, so we are not able to retrieve the table and the data. – aaru11 Jun 16 '17 at 5:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.