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.

nicholaswmin
  • 21,686
  • 15
  • 91
  • 167

3 Answers3

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.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
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)
jleach
  • 7,410
  • 3
  • 33
  • 60
  • I've ommited the full Trigger body - I'm actually using the 1st style (and operating outside SSMS) - Editing now – nicholaswmin Nov 30 '16 at 11:41
  • The key is to enclose the body in BEGIN ... END. I've update the answer to better demonstrate. – jleach Nov 30 '16 at 11:43
  • I think I'm already doing this correctly - I've updated the code in my post – nicholaswmin Nov 30 '16 at 11:44
  • @jdl134679 that's not what the OP asks. The statements are valid. Most likely though, `CREATE` statements can't be combined. – Panagiotis Kanavos Nov 30 '16 at 11:45
  • @PanagiotisKanavos any ideas on how to create multiple triggers in one go then? – nicholaswmin Nov 30 '16 at 11:46
  • @jdl134679 and `GO` is used by SSMS and sqlcmd only, and is only used as a placeholder to separate individual executable scripts. Each time SSMS sees `GO` it sends the text up to it to the server. The equivalent for a client would be to send multiple commands – Panagiotis Kanavos Nov 30 '16 at 11:46
  • @PanagiotisKanavos ain't that a bummer - Thanks, care to post it as an answer? – nicholaswmin Nov 30 '16 at 11:48
  • See updated answer. I use this for audit tables all the time within a single statement to create three different triggers. – jleach Nov 30 '16 at 11:50
  • @NicholasKyriakides what exactly are you trying to do? Why don't you create one script for the entire database, with batch delimiters (ie `GO`)? It's a lot easier to version creation/migration scripts than try to find out what changed in generated scripts – Panagiotis Kanavos Nov 30 '16 at 11:50
  • @jdl134679 you simply *can't* put multiple `CREATE TRIGGER` statements in the same batch. The updated answer still won't work – Panagiotis Kanavos Nov 30 '16 at 11:51
  • @PanagiotisKanavos I use that code for creating audit triggers all the time. Seems to work just fine (it's part of a stored procedure to create triggers on target tables). – jleach Nov 30 '16 at 11:52
  • @jdl134679 let me guess - you use GO and SSMS, or a `CREATE TABLE` and a single `CREATE TRIGGER`. The [docs explain](https://technet.microsoft.com/en-us/library/ms175502.aspx) that you can't use multiple CREATE TRIGGER statements – Panagiotis Kanavos Nov 30 '16 at 11:57
  • @PanagiotisKanavos - I do not. http://pastebin.com/HMGJekAm Check the last three lines of the sproc posted there – jleach Nov 30 '16 at 12:01
  • @jdl134679 !!! you don't execute ANY `CREATE TRIGGER` statement in there! You use dynamic SQL to execute a *different* script. Try *really* running two `CREATE TRIGGER` statements, no `EXEC` or `GO` – Panagiotis Kanavos Nov 30 '16 at 12:03
  • @PanagiotisKanavos - yea? That's what I posted in the answer that you said wouldn't work? – jleach Nov 30 '16 at 12:04
  • I suspect you misunderstand what `EXEC` does – Panagiotis Kanavos Nov 30 '16 at 12:05
  • I certainly don't know everything. Are you saying that OP cannot wrap his trigger creates in dynamic SQL and execute them within a single batch as I've shown? (as it works fine within the context of a stored procedure, my _assumption_ was that he'd be able to use the same technique from his point of generation) – jleach Nov 30 '16 at 12:13
  • @jdl134679 why would anyone do that? You can't even verify such statements. Never mind the injection risk - a single `,` in a parameter will break the script. Or the unnecessary double and triple quoting required to get a valid dynamic SQL statement. Possible conversion errors. – Panagiotis Kanavos Nov 30 '16 at 12:16
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.

MgSam
  • 12,139
  • 19
  • 64
  • 95
  • 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. – Shaggie Apr 30 '23 at 03:27