sql-servert-sqltransactionstry-catchdatabase-trigger

How to put CREATE TRIGGER into TRY-CATCH block with TRANSACTION?


I'm not advanced programmer in SQL and maybe my question is silly, but I haven't found an answer in google. We have some SQL construction for implementing packages of changes:

...
BEGIN TRY
    BEGIN TRANSACTION;
    <User Code Is Here>
    ...
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ...
END CATCH;
...

How can I put the chain of CREATE TRIGGER blocks instead of <User Code Is Here> without errors:

-- Table1
CREATE TRIGGER trTable1_Dates ON dbo.Table1
AFTER INSERT, UPDATE
AS
BEGIN
   SET NOCOUNT ON;
   ...
   SET NOCOUNT OFF;
END
GO
...
-- TableN
CREATE TRIGGER trTableN_Dates ON dbo.TableN
AFTER INSERT, UPDATE
AS
BEGIN
   SET NOCOUNT ON;
   ...
   SET NOCOUNT OFF;
END
GO

The purpose is to create all triggers or nothing and print message in CATCH block of code if fails.

Edited

The errors are:

  1. On first trigger's BEGIN: SQL80001: Incorrect syntax near 'BEGIN'. Expecting EXTERNAL.
  2. After first trigger, on GO: SQL80001: Incorrect syntax near 'GO'.
  3. END TRY: SQL80001: Incorrect syntax near 'TRY'. Expecting CONVERSATION.
  4. END CATCH: SQL80001: Incorrect syntax near 'CATCH'. Expecting CONVERSATION.

Solution

  • You need to run each create trigger statement in a separate scope/batch (because it has to be the first statement in the batch). So you'll have to escape any quotes in the trigger definitions too:

    BEGIN TRY
        BEGIN TRANSACTION;
        exec sp_executesql N'CREATE TRIGGER trTable1_Dates ON dbo.Table1
        AFTER INSERT, UPDATE
        AS
        BEGIN
           SET NOCOUNT ON;
           //An empty string in here has to be '''' to escape the quotes
           SET NOCOUNT OFF;
        END'
        
        exec sp_executesql N'CREATE TRIGGER trTableN_Dates ON dbo.TableN
        AFTER INSERT, UPDATE
        AS
        BEGIN
           SET NOCOUNT ON;
           ...
           SET NOCOUNT OFF;
        END'
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ...
    END CATCH;
    

    Transactions are orthogonal to batches and nested scopes, so the transaction covers all activity that occurs inside each EXEC too.