Styjun

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.
   

You need to run each create table 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.