动态SQL错误：“CREATE TRIGGER”必须在查询批处理的第一条语句(dynamic sql

由于一些管理任务的一部分，我们有很多表，每个需要创建的触发器。当对象已被修改的触发器将设置一个标志和日期的审核数据库。为简单起见，我有需要创建的触发器的所有对象的表。

我想产生一些动态SQL来为每个对象做到这一点，但我得到这个错误：
'CREATE TRIGGER' must be the first statement in a query batch.

这里是生成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

如果我使用PRINT并将代码粘贴到一个新的查询窗口，代码执行，没有任何问题。

我已经删除了GO语句，因为这也给错误。

我在想什么？
为什么我会得到使用错误EXEC(@sql); 甚至EXEC sp_executesql @sql; ？
这是什么做的范围内EXEC()
非常感谢您的帮助。

Answer 1:

如果您使用SSMS（或其他类似的工具）来运行该脚本生成的代码，你会得到完全同样的错误。它可以运行，当你插入分隔符批次没事（ GO ），但现在不这样做，你会面对SSMS同样的问题了。

在另一方面，你为什么不能把原因GO在动态脚本是因为GO不是一个SQL语句，它只是通过SSMS和其他一些工具所识别的分隔符。也许你已经意识到这一点。

无论如何，点GO是工具知道，代码应拆分及其部件单独运行。而这，分开，是你应该在你的代码做什么为好。

所以，你有以下选择：

插入EXEC sp_execute @sql只是降低触发后的部分，然后重置价值@sql ，以便然后存储和运行反过来定义部分;
使用两个变量， @sql1和@sql2 ，DROP部分存储IF EXISTS /成@sql1 ，在CREATE TRIGGER一进@sql2 ，然后运行这两个脚本（再次，单独）。

但后来，因为你已经发现了，你要面对另一个问题：你不能没有在该数据库的上下文中运行的语句创建另一个数据库的触发器。

现在，有提供必要的上下文的2种方式：

1）使用USE语句;

2）运行作为使用动态查询语句（多个） EXEC targetdatabase ..sp_executesql N'…'

显然，第一个选项是不会在这里工作，我们不能增加USE …之前CREATE TRIGGER ，因为后者必须是批处理的语句。

第二个选项可以使用，但它需要动态性的附加层（不知道这是否是一个字）。这是因为该数据库名是一个参数在这里，所以我们需要运行EXEC targetdatabase ..sp_executesql N'…' 作为一个动态脚本，因为实际的脚本运行本身应该是一个动态脚本，因此，它会两次嵌套。

因此，（第二）之前EXEC sp_executesql @sql; 行添加以下内容：

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

正如你所看到的，以内容集成@sql正确的嵌套的动态脚本，它们必须用单引号。出于同样的原因，在每一个单引号@sql必须加倍（例如，使用的REPLACE()函数，如在上面的语句）。

Answer 2:

触发创作必须在自己的执行批次进行。你是一个过程里面，所以你将无法创建它。

我建议增加@sql到一个临时表，然后一旦PROC是完成生成所有语句，循环这个临时表来执行它们，并创建触发器

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