locked
ignore error in DDL trigger? RRS feed

  • Question

  • i have a situation where I want indexes auto created when a table with a certain name is created. i have the trigger created and it works fine so long as there are no errors in creating the index.

    however, when there are errors it rolls the entire transaction back, including the create table. I dont want the create table to fail. I am using try/catch but it seems like no matter what I do I cannot get it ignore the create index failure.

    is there some setting I can turn on to get this to work?

     

     

     

    IF EXISTS (SELECT * FROM sys.server_triggers

        WHERE name = 'ddl_trig_database')

    DROP TRIGGER ddl_trig_database

    ON ALL SERVER;

    GO

    CREATE TRIGGER ddl_trig_database 

    ON ALL SERVER 

    FOR CREATE_TABLE 

    AS 

        DECLARE @data XML = EVENTDATA()

        SET XACT_ABORT OFF;

     

        DECLARE @DatabaseName nvarchar(max) = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(max)')

        DECLARE @SchemaName nvarchar(max) = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(max)')

        DECLARE @ObjectName nvarchar(max) = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)')

        PRINT @DatabaseName

        PRINT @SchemaName

        PRINT @ObjectName

     

     

        DECLARE @Command varchar(MAX)

        DECLARE @Compress bit

     

        DECLARE c CURSOR LOCAL FOR SELECT CreateIndexCommand, Compress FROM dba.dbo.PostCreateTableCommands

    WHERE DatabaseName = @DatabaseName AND SchemaName = @SChemaName AND TableName = @ObjectName

    OPEN c 

     

    FETCH NEXT FROM c INTO @Command, @Compress

     

    WHILE @@FETCH_STATUS = 0

    BEGIN

     

    DECLARE @SQL varchar(MAX) = ''

     

    BEGIN TRY

     

    IF @Compress = 1

    BEGIN

    SET @SQL = 'ALTER TABLE ' + @DatabaseName + '.' + @SchemaName + '.' + @ObjectName + ' REBUILD WITH(DATA_COMPRESSION=PAGE)'

    EXEC (@SQL)

    END

     

    SET @SQL = 'USE ' + @DatabaseName + ' ' + @Command

    EXEC (@SQL)

     

    END TRY

    BEGIN CATCH

     

    PRINT ERROR_MESSAGE()

     

    END CATCH;

     

    FETCH NEXT FROM c INTO @Command, @Compress

     

    END

    GO

    --DROP TRIGGER ddl_trig_database ON ALL SERVER;

    GO

     


    Craig
    • Edited by CraigL77 Tuesday, June 15, 2010 6:17 PM
    Tuesday, June 15, 2010 5:53 PM

Answers

  • i have a situation where I want indexes auto created when a table with a certain name is created. i have the trigger created and it works fine so long as there are no errors in creating the index.

    however, when there are errors it rolls the entire transaction back, including the create table. I dont want the create table to fail. I am using try/catch but it seems like no matter what I do I cannot get it ignore the create index failure.

    Hi,

    From your description, I suggest you create indexes outside the DDL trigger. 

    For example, we can:

    1) Store the event information to another table when the DDL trigger fires:

    CREATE TABLE [msdb].[dbo].[TableCreationLog] 
    (
        ID INT IDENTITY(1,1),
        InsertDateTime DATETIME, 
        XmlEventData XML,
        IndexCreated BIT
    )
    GO
    
    CREATE TRIGGER ddl_trig_database 
    ON ALL SERVER 
    FOR CREATE_TABLE 
    AS 
    BEGIN
        
      DECLARE @Data XML = EVENTDATA()
    
      DECLARE @DatabaseName NVARCHAR(MAX) = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(MAX)')
      DECLARE @SchemaName NVARCHAR(MAX) = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(MAX)')
      DECLARE @ObjectName NVARCHAR(MAX) = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(MAX)')
    
        IF NOT EXISTS (SELECT * FROM [dba].[dbo].[PostCreateTableCommands] 
    	WHERE DatabaseName = @DatabaseName AND SchemaName = @SChemaName AND TableName = @ObjectName) 
        BEGIN
           INSERT INTO [msdb].[dbo].[TableCreationLog] 
           VALUES (GETDATE(),@Data,0)    
        END
    END
    GO

    2) Create a stored procedure to retrieve data from tables and create indexes:

    CREATE PROCEDURE CreateTrigger 
    AS
    BEGIN
        --DECLARE c CURSOR LOCAL FOR 
        --SELECT tcl.ID,tcl.XmlEventData,pctc.CreateIndexCommand,pctc.Compress 
        --FROM [msdb].[dbo].[TableCreationLog] tcl
        --INNER JOIN [dba].[dbo].[PostCreateTableCommands] pctc
        --ON tcl.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(MAX)') = pctc.DatabaseName
        --AND tcl.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(MAX)') = pctc.SchemaName
        --AND tcl.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(MAX)') = pctc.TableName
        --WHERE tcl.IndexCreated = 0   
        
        --OPEN C
        
        --FETCH NEXT FROM 
        
        --WHILE @@FETCH_STATUS = 0
        --BEGIN
           --CREATE INDEX
           
           --UPDATE [msdb].[dbo].[TableCreationLog] SET IndexCreated=1
           --WHERE ID = @ID
           
           --FETCH NEXT FROM
        --ENDs
    END
    GO

    3) Schedule a job in SQL Server to execute the stored procedure repeatedly.

        How to: Schedule a Job (SQL Server Management Studio)
        http://msdn.microsoft.com/en-us/library/ms191439.aspx

    In this way, table creations will not be rolled back if an error occurs when creating indexes.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Proposed as answer by KJian_ Tuesday, June 22, 2010 8:14 AM
    • Marked as answer by KJian_ Thursday, June 24, 2010 7:27 AM
    Friday, June 18, 2010 7:32 AM

All replies

  • Also, there was a suggestion on the forums to create a wrapper SP and handle the error in the SP. That only works in table triggers. It apparently doesnt work in DDL triggers
    Craig
    Tuesday, June 15, 2010 6:08 PM
  • i have a situation where I want indexes auto created when a table with a certain name is created. i have the trigger created and it works fine so long as there are no errors in creating the index.

    however, when there are errors it rolls the entire transaction back, including the create table. I dont want the create table to fail. I am using try/catch but it seems like no matter what I do I cannot get it ignore the create index failure.

    Hi,

    From your description, I suggest you create indexes outside the DDL trigger. 

    For example, we can:

    1) Store the event information to another table when the DDL trigger fires:

    CREATE TABLE [msdb].[dbo].[TableCreationLog] 
    (
        ID INT IDENTITY(1,1),
        InsertDateTime DATETIME, 
        XmlEventData XML,
        IndexCreated BIT
    )
    GO
    
    CREATE TRIGGER ddl_trig_database 
    ON ALL SERVER 
    FOR CREATE_TABLE 
    AS 
    BEGIN
        
      DECLARE @Data XML = EVENTDATA()
    
      DECLARE @DatabaseName NVARCHAR(MAX) = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(MAX)')
      DECLARE @SchemaName NVARCHAR(MAX) = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(MAX)')
      DECLARE @ObjectName NVARCHAR(MAX) = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(MAX)')
    
        IF NOT EXISTS (SELECT * FROM [dba].[dbo].[PostCreateTableCommands] 
    	WHERE DatabaseName = @DatabaseName AND SchemaName = @SChemaName AND TableName = @ObjectName) 
        BEGIN
           INSERT INTO [msdb].[dbo].[TableCreationLog] 
           VALUES (GETDATE(),@Data,0)    
        END
    END
    GO

    2) Create a stored procedure to retrieve data from tables and create indexes:

    CREATE PROCEDURE CreateTrigger 
    AS
    BEGIN
        --DECLARE c CURSOR LOCAL FOR 
        --SELECT tcl.ID,tcl.XmlEventData,pctc.CreateIndexCommand,pctc.Compress 
        --FROM [msdb].[dbo].[TableCreationLog] tcl
        --INNER JOIN [dba].[dbo].[PostCreateTableCommands] pctc
        --ON tcl.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(MAX)') = pctc.DatabaseName
        --AND tcl.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(MAX)') = pctc.SchemaName
        --AND tcl.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(MAX)') = pctc.TableName
        --WHERE tcl.IndexCreated = 0   
        
        --OPEN C
        
        --FETCH NEXT FROM 
        
        --WHILE @@FETCH_STATUS = 0
        --BEGIN
           --CREATE INDEX
           
           --UPDATE [msdb].[dbo].[TableCreationLog] SET IndexCreated=1
           --WHERE ID = @ID
           
           --FETCH NEXT FROM
        --ENDs
    END
    GO

    3) Schedule a job in SQL Server to execute the stored procedure repeatedly.

        How to: Schedule a Job (SQL Server Management Studio)
        http://msdn.microsoft.com/en-us/library/ms191439.aspx

    In this way, table creations will not be rolled back if an error occurs when creating indexes.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Proposed as answer by KJian_ Tuesday, June 22, 2010 8:14 AM
    • Marked as answer by KJian_ Thursday, June 24, 2010 7:27 AM
    Friday, June 18, 2010 7:32 AM