2

I'm trying to send a batch of CREATE TRIGGER statements as a string to be processed when migrating my DB

CREATE TRIGGER [dbo].[triggerBar] ON [dbo].[tableBar]
INSTEAD OF UPDATE,INSERT AS
BEGIN
SET NOCOUNT ON
 -- Trigger body here..
END;

CREATE TRIGGER [dbo].[triggerFoo] ON [dbo].[tableFoo]
INSTEAD OF UPDATE,INSERT AS
BEGIN
SET NOCOUNT ON
  -- Trigger body here..
END;

So I'm delimiting each statement block with ; but I still get this error:

Incorrect syntax near the keyword 'TRIGGER'

When sending just the 1st Trigger is works just fine. Not sure what's wrong.

Improve this question
4
  • How are you executing the statements? With SSMS and sqlcmd you need to put a GO statement from one DDL statement to the next Commented Nov 30, 2016 at 11:38
  • @PanagiotisKanavos I'm sending them as a string via the MSSQL Node.js driver Commented Nov 30, 2016 at 11:39
  • You can put multiple DML statements in a single batch. I'm not sure about DDLs though. It's not a matter of using the correct delimiter Commented Nov 30, 2016 at 11:42
  • @PanagiotisKanavos In that case why is it triggering an Incorrect Syntax error? Commented Nov 30, 2016 at 11:43

3 Answers 3

Reset to default
3

According to the documentation on batches you can't put multiple CREATE TRIGGER statements in the same batch:

CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must start the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.

GO works because it's a batch delimiter recognized by SSMS, sqlcmd and SQL Server Development Tools that is never sent to the server. The tool uses it to split the text in batches and send them one by one to the server. Transactions work across batches (it's the same connection after all), so it's possible to rollback certain DDL statements.

I'll assume that you want to create and execute a database creation script from Node.

One solution is to the same approach as the SQL Server tools: generate separate batches and execute them one by one against the database.

Another option is to create a single SQL script with GO delimiters, and execute it using SQL Server's command line tools. This is more maintainable, because you can save and version the script, detect changes etc.

A third option is to use SQL Server's Development Tools to model your database. The database projects support versioning and validating. The main advantage though is that SSDT can generate a script to update a target database, similar to what Redgate's tools do. SSDT is moderately smart and can recognize renames etc that are performed inside the tool itself and use eg sp_rename instead of dropping one column and creating a new one.

A further advantage is that SSDT generates a dacpac, essentially a compiled model that can be used to diff against a target database and generate the update script.

This makes continuous database deployments a lot easier. It's supported by AppVeyor, TFS. TeamCity and any tool/service that can run the sqlpackage tool.

The disadvantage is that SSDT works only for SQL Server databases.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Certain statements can have nothing before them. In that case, within SSMS, use GO to indicate the end of a batch (this effectively clears the buffers, so any variables declared before the GO will be wiped).

If you're in SSMS, you can:

CREATE TRIGGER ... AS ...
GO
CREATE TRIGGER ... etc

If you're outside of SSMS, you have to either send separate commands, or put them into a string in T-SQL and execute the string:

DECLARE @Trig1 NVARCHAR(MAX)
DECLARE @Trig2 NVARCHAR(MAX)
DECLARE @Trig3 NVARCHAR(MAX)
SET @Trig1 = 'CREATE TRIGGER ...'
SET @Trig2 = 'CREATE TRIGGER ...'
SET @Trig3 = 'CREATE TRIGGER ...'
EXEC (@Trig1)
EXEC (@Trig2)
EXEC (@Trig3)
Improve this answer

18 Comments

I've ommited the full Trigger body - I'm actually using the 1st style (and operating outside SSMS) - Editing now
The key is to enclose the body in BEGIN ... END. I've update the answer to better demonstrate.
I think I'm already doing this correctly - I've updated the code in my post
@jdl134679 that's not what the OP asks. The statements are valid. Most likely though, CREATE statements can't be combined.
@PanagiotisKanavos any ideas on how to create multiple triggers in one go then?
|
1

For anyone looking to do this- GO is not always a good solution for running multiple batches because it's not T-SQL, it's only understood by SSMS and some command line tools. For instance, you can't use it in a stored procedure or function.

Does this mean stored procedures and functions are limited to a single batch?

It's rarely mentioned outside of hushed tones in conference halls, but you can actually run multiple batches within a single T-SQL script that is guaranteed to work anywhere. Dynamic SQL. EXEC sp_executesql @sql always run within its own batch. You can furthermore surround multiple EXEC sp_executesql calls with a transaction if you need to. Dynamic SQL is a pain to write but it lets you do things like multiple batches in a sproc which would otherwise be impossible.

Improve this answer

1 Comment

I prefer to avoid using sp_executesql when possible as it can cause adverse effects. In this case, "EXEC (@sql)" works perfectly. Note that the brackets ARE required.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.