[SOLVED] Separating multiple SQL Server statements running in batch

Separating multiple SQL Server statements running in batch

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.

Solution

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.