Sql dynamic error: "CREATE TRIGGER" must be the first statement in a query package

Question

Within some administrative tasks, we have many tables, each of which requires the creation of a trigger. The trigger sets the flag and date in the audit database when the object was modified. For simplicity, I have a table with all the objects for which triggers are created.

I try to create dynamic sql for this for each object, but I get this error:
'CREATE TRIGGER' must be the first statement in a query batch.

Here is the code to create 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 into a new query window, the code runs without any problems.

I deleted the GO statements, as this also led to errors.

What am I missing?
Why am I getting an error using EXEC(@sql); or even EXEC sp_executesql @sql; ?
Is this somehow related to the context inside EXEC() ?
Thanks so much for any help.

Answer 1

If you use SSMS (or another similar tool) to run the code generated by this script, you will get exactly the same error. When batch separators ( GO ) are enabled, it may work fine, but now that you do not, you will encounter the same problem in SSMS too.

On the other hand, the reason you cannot put GO in your dynamic scripts is because GO not an SQL statement, but simply a delimiter recognized by SSMS and some other tools. You may already know about this.

In any case, the GO point is designed to let the tool know that the code should be split, and its parts are executed separately. And this is separate, this is what you should do in your code.

So, you have the following options:

• insert EXEC sp_execute @sql immediately after the part that resets the trigger, then reset the @sql value to then save and run the definition part in turn;

• use two variables, @sql1 and @sql2 , save the IF EXISTS / DROP part to @sql1 , CREATE TRIGGER one in @sql2 , then run both scripts (again, separately).

But then, as you already found out, you will encounter another problem: you cannot create a trigger in another database without running the instruction in the context of this database.

Now there are two ways to provide the necessary context:

1) use the USE instruction;

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

Obviously, the first option will not work here: we cannot add USE … until CREATE TRIGGER , because the latter should be the only statement in the package.

You can use the second option, but this will require an additional level of dynamism (not sure if this word). This is because here the database name is a parameter, so we need to run EXEC targetdatabase ..sp_executesql N'…' as a dynamic script, and since the actual script itself needs to be a dynamic script, it will therefore be nested twice.

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

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

As you can see, to properly include the contents of @sql as a nested dynamic script, they must be enclosed in single quotes. For the same reason, every single quote in @sql should be doubled (for example, using the REPLACE() function , as in the statement above).

Answer 2

The creation of a trigger must be performed in its own execution part. You are inside the procedure, so you cannot create it.

I suggest adding @sql to the temporary table, and then, as soon as proc finishes creating all the statements, loop this temp table to execute them and create triggers