动态 SQL 错误: 'CREATE TRIGGER' 必须是查询批处理中的第一个语句。

Question

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

我正在尝试生成一些动态SQL来为每个对象执行此操作，但是我遇到了以下错误：
'CREATE TRIGGER'必须是查询批处理中的第一条语句。 这是生成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()内的上下文有关吗？非常感谢任何帮助。

- MarkusBee

Answer 1

如果你使用SSMS（或类似的工具）来运行此脚本生成的代码，你将得到完全相同的错误。插入批次分隔符（GO）时可能会运行得很好，但现在不插入，则在SSMS中也会遇到相同的问题。

另一方面，你不能在动态脚本中添加GO的原因是，GO不是SQL语句，它只是由SSMS和其他一些工具识别的分隔符。可能你已经知道了这一点。

无论如何，GO的作用是让工具知道代码应该被拆分，并且其部分应该被单独运行。那么，在你的代码中，你也应该这样做。

所以，你有以下几个选项：

1、在删除触发器的部分之后插入EXEC sp_execute @sql，然后重置@sql的值，以便存储并依次运行定义部分；

2、使用两个变量@sql1和@sql2，将IF EXISTS/DROP部分存储到@sql1中，将CREATE TRIGGER部分存储到@sql2中，然后运行两个脚本（同样，是分开运行）。

但是，正如你已经发现的那样，你将面临另一个问题：如果没有在该数据库上下文中运行语句，就无法在另一个数据库中创建触发器。

现在，提供必要上下文有两种方法：

1、使用USE语句；

2、使用EXEC targetdatabase..sp_executesql N'…'作为动态查询来运行语句。

显然，第一种选项在这里不起作用：我们不能在CREATE TRIGGER之前添加USE …，因为后者必须是批处理中唯一的语句。

第二个选项可以使用，但它将需要一个额外的动态性层次（不确定是否是一个单词）。这是因为数据库名称是一个参数，所以我们需要将EXEC targetdatabase..sp_executesql N'…'作为动态脚本运行，并且由于要运行的实际脚本本身应该是一个动态脚本，因此它将被嵌套两次。

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

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

如您所见，为了正确地将@sql的内容作为嵌套动态脚本集成，必须将其包含在单引号中。出于相同的原因，在@sql中的每个引号符号内部都必须加倍（例如使用REPLACE()函数，如上述语句所示）。

- Andriy M

---

非常感谢您。我现在已经按照您在上面的第一个选项中建议的那样，将代码分成了两个“部分”，如下所示： - MarkusBee

非常感谢。我已按照您在第一种选择中建议的将代码分成两个“部分”。第一部分执行得很完美。 我将澄清该过程是从“审核”数据库执行的，需要触发器的对象位于其他数据库中。 现在执行CREATE TRIGGER语句会抛出以下错误，即使使用了完全限定的表名： “无法在[...]上创建触发器，因为目标不在当前数据库中。” 有没有什么办法解决这个问题？如何在另一个数据库的上下文中执行它？ 谢谢。 - MarkusBee

@markb：请查看我的更新。我不确定是否一切都像我想要的那样清晰，所以请随时提问。 - Andriy M

这非常好，非常清晰，正是我想要的。 唯一的问题 - 我在 BOL 中检查了一下 - 是 QUOTENAME 函数，其中字符串参数限制为 128 个字符。大于 128 个字符的输入返回 NULL，因此我用引号代替了该函数。 现在我可以看到 'EXEC'+@dbname+'..sp_executesql' 可以非常强大，并允许在另一个上下文/数据库中执行动态 SQL。 再次感谢您的帮助。 - MarkusBee

@markb：啊，确实是我的错。你可以用类似于'''' + REPLACE(@sql, '''', '''''') + ''''的东西来替换QUOTENAME的功能。(也就是说，我认为仅仅将@sql放在引号中是不够的，但因为触发器定义中有字符串表达式，所以你还需要在那里将每个引号都加倍。) - Andriy M

啊，没错，我忘记了字符串中的字符串！REPLACE（）完美地完成了工作。它运行得非常顺利！你刚刚让我的周末变得愉快 :) - MarkusBee

Answer 2

这种方法在使用动态SQL内部的动态SQL时更好 - 副作用是：无需替换引号和类似内容。

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

- Jonas Brock

Answer 3

触发器的创建必须在其自己的执行批处理中完成。您现在正在一个过程内部，因此无法创建它。

我建议将@sql添加到临时表中，然后一旦过程完成生成所有语句，循环此临时表以执行它们并创建触发器。

- Diego