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:
BEGIN
: SQL80001: Incorrect syntax near 'BEGIN'. Expecting EXTERNAL.GO
: SQL80001: Incorrect syntax near 'GO'.END TRY
: SQL80001: Incorrect syntax near 'TRY'. Expecting CONVERSATION.END CATCH
: SQL80001: Incorrect syntax near 'CATCH'. Expecting CONVERSATION.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.