dynamic sql error: 'CREATE TRIGGER' must be the first statement in a query batch

dynamic-sql, sql-server-2008, triggers

Solution

If you use SSMS (or other similar tool) to run the code produced by this script, you will get exactly the same error. It could run all right when you inserted batch delimiters (`GO`), but now that you don't, you'll face the same issue in SSMS too.

On the other hand, the reason why you cannot put `GO` in your dynamic scripts is because `GO` isn't a SQL statement, it's merely a delimiter recognised by SSMS and some other tools. Probably you are already aware of that.

Anyway, the point of `GO` is for the tool to know that the code should be split and its parts run separately. And that, separately, is what you should do in your code as well.

So, you have these options:

insert `EXEC sp_execute @sql` just after the part that drops the trigger, then reset the value of `@sql` to then store and run the definition part in its turn;

use two variables, `@sql1` and `@sql2`, store the IF EXISTS/DROP part into `@sql1`, the CREATE TRIGGER one into `@sql2`, then run both scripts (again, separately).

But then, as you've already found out, you'll face another issue: you cannot create a trigger in another database without running the statement in the context of that database.

Now, there are 2 ways of providing the necessary context:

1) use a `USE` statement;

2) run the statement(s) as a dynamic query using `EXEC targetdatabase..sp_executesql N'…'`.

Obviously, the first option isn't going to work here: we cannot add `USE …` before `CREATE TRIGGER`, because the latter must be the only statement in the batch.

The second option can be used, but it will require an additional layer of dynamicity (not sure if it's a word). It's because the database name is a parameter here and so we need to run `EXEC targetdatabase..sp_executesql N'…'` as a dynamic script, and since the actual script to run is itself supposed to be a dynamic script, it, therefore, will be nested twice.

So, before the (second) `EXEC sp_executesql @sql;` line add the following:

SET @sql = N'EXEC ' + @dbname + '..sp_executesql N'''
           + REPLACE(@sql, '''', '''''') + '''';

As you can see, to integrate the contents of `@sql` as a nested dynamic script properly, they must be enclosed in single quotes. For the same reason, every single quotation mark in `@sql` must be doubled (e.g. using the `REPLACE()` function, as in the above statement).

Problem

As part of some administrative tasks, we have many tables that each need a trigger created. The trigger will set a flag and the date in the Audit database when an object has been modified. For simplicity, I have a table with all the objects that need triggers created. I am trying to generate some dynamic sql to do this for each object, but I am getting this error: `'CREATE TRIGGER' must be the first statement in a query batch.` Here is the code to generate the sql. ``` CREATE PROCEDURE [spCreateTableTriggers] AS BEGIN DECLARE @dbname varchar(50), @schemaname varchar(50), @objname varchar(150), @objtype varchar(150), @sql nvarchar(max), @CRLF varchar(2) SET @CRLF = CHAR(13) + CHAR(10); DECLARE ObjectCursor CURSOR FOR SELECT DatabaseName,SchemaName,ObjectName FROM Audit.dbo.ObjectUpdates; SET NOCOUNT ON; OPEN ObjectCursor ; FETCH NEXT FROM ObjectCursor INTO @dbname,@schemaname,@objname; WHILE @@FETCH_STATUS=0 BEGIN SET @sql = N'USE '+QUOTENAME(@dbname)+'; ' SET @sql = @sql + N'IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'''+QUOTENAME(@schemaname)+'.[Tiud_'+@objname+'_AuditObjectUpdates]'')) ' SET @sql = @sql + N'BEGIN DROP TRIGGER '+QUOTENAME(@schemaname)+'.[Tiud_'+@objname+'_AuditObjectUpdates]; END; '+@CRLF SET @sql = @sql + N'CREATE TRIGGER '+QUOTENAME(@schemaname)+'.[Tiud_'+@objname+'_AuditObjectUpdates] '+@CRLF SET @sql = @sql + N' ON '+QUOTENAME(@schemaname)+'.['+@objname+'] '+@CRLF SET @sql = @sql + N' AFTER INSERT,DELETE,UPDATE'+@CRLF SET @sql = @sql + N'AS '+@CRLF SET @sql = @sql + N'IF EXISTS(SELECT * FROM Audit.dbo.ObjectUpdates WHERE DatabaseName = '''+@dbname+''' AND ObjectName = '''+@objname+''' AND RequiresUpdate=0'+@CRLF SET @sql = @sql + N'BEGIN'+@CRLF SET @sql = @sql + N' SET NOCOUNT ON;'+@CRLF SET @sql = @sql + N' UPDATE Audit.dbo.ObjectUpdates'+@CRLF SET @sql = @sql + N' SET RequiresUpdate = 1'+@CRLF SET @sql = @sql + N' WHERE DatabaseName = '''+@dbname+''' '+@CRLF SET @sql = @sql + N' AND ObjectName = '''+@objname+''' '+@CRLF SET @sql = @sql + N'END' +@CRLF SET @sql = @sql + N'ELSE' +@CRLF SET @sql = @sql + N'BEGIN' +@CRLF SET @sql = @sql + N' SET NOCOUNT ON;' +@CRLF SET @sql = @sql + @CRLF SET @sql = @sql + N' -- Update ''SourceLastUpdated'' date.'+@CRLF SET @sql = @sql + N' UPDATE Audit.dbo.ObjectUpdates'+@CRLF SET @sql = @sql + N' SET SourceLastUpdated = GETDATE() '+@CRLF SET @sql = @sql + N' WHERE DatabaseName = '''+@dbname+''' '+@CRLF SET @sql = @sql + N' AND ObjectName = '''+@objname+''' '+@CRLF SET @sql = @sql + N'END; '+@CRLF --PRINT(@sql); EXEC sp_executesql @sql; FETCH NEXT FROM ObjectCursor INTO @dbname,@schemaname,@objname; END CLOSE ObjectCursor ; DEALLOCATE ObjectCursor ; END ``` If I use `PRINT` and paste the code to a new query window, the code executes without any problem. I have removed the `GO` statements as this was also giving errors. What am I missing? Why am I getting an error using `EXEC(@sql);` or even `EXEC sp_executesql @sql;`? Is this something to do with the context within `EXEC()`? Many thanks for any help.

Original source