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

Question

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.

Answer 1

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).

Answer 2

a trigger creation must be done on its own execution batch. You are inside a procedure so you wont be able to create it.

I suggest adding the @sql into a temp table and then once the proc is finish generating all of the statements, loop this temp table to execute them and create the triggers

Answer 3

This approach is a much better approach when using dynamic sql within dynamic sql - side effect: no need to replace quotes and a like:

DECLARE
    @originalsql NVARCHAR(4000) = N' ..... '
    , @stmt NVARCHAR(200) = 'otherdatabase.dbo.sp_executesql @stmt = @sql'
    , @params NVARCHAR(200) = '@sql NVARCHAR(4000)'

EXECUTE sp_executesql @stmt=@stmt, @params=@params, @sql = @originalsql