Create sql trigger dynamically and rollback if error

Question

I'm creating a stored procedure that will create 3 triggers (insert, update, delete) given a table name.

Here is an example to illustrate the issue I'm experiencing :

CREATE PROCEDURE [dbo].[sp_test] 
AS
BEGIN

    BEGIN TRAN

    -- Create trigger 1
    DECLARE @sql NVARCHAR(MAX) = 'CREATE TRIGGER test1 ON  TableXML AFTER INSERT AS BEGIN END'
    EXEC sp_executesql @sql

    -- Create trigger 2, but this one will fail because Table_1 contain an ntext field.
    SET @sql = 'CREATE TRIGGER test1 ON  Table_1 AFTER INSERT AS 
                BEGIN
                    select * from inserted
                END'
    EXEC sp_executesql @sql

    COMMIT TRAN

END

So I thought that wrapping the call in a transaction, the first trigger won't be created. Since the second will fail. BUT the first trigger is created anyway .... How can I prevent this from happening. I want the whole thing to be atomics.

Answer 1

You have no error handling or rollback statement in your procedure.

Answer 2

In some databases, DDL statements such as CREATE TRIGGER will automatically commit themselves; if sql-server is one of them, you can't. (This is true of Oracle and MySQL; not true of RDB; not sure about sql-server.)

Answer 3

Try this, with BEGIN TRY

CREATE PROCEDURE [dbo].[sp_test] 
AS
BEGIN

    BEGIN TRY
        BEGIN TRAN

        DECLARE @sql NVARCHAR(MAX) = 'CREATE TRIGGER test1 ON  TableXML AFTER INSERT AS BEGIN END'
        EXEC sp_executesql @sql

        SET @sql = 'CREATE TRIGGER test1 ON  Table_1 AFTER INSERT AS 
                    BEGIN
                        select * from inserted
                    END'
        EXEC sp_executesql @sql

        COMMIT TRAN
    END TRY
    BEGIN CATCH
        RAISERROR('Errormessage', 18, 1)
        ROLLBACK TRAN
    END CATCH

END

Answer 4

You don't have a Rollback call on an error.

Using SQL Server's Try/Catch, you could do something like what Vidar mentioned, or you if Sql Server automatically commits triggers (as Brian H mentioned as a posibility) you could instead have in your Catch block:

BEGIN CATCH
   RAISERROR('Errormessage', 18, 1)
   DROP Trigger test1
END CATCH