Create sql trigger dynamically and rollback if error

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

    -- Creat开发者_C百科e 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.

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

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

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.)

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