sql-server-2008 - 动态SQL错误: 'CREATE TRIGGER' must be the first statement in a query batch

Question

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

我正在尝试为每个对象生成一些动态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，将IF EXISTS/DROP部分存储到@sql1中，将CREATE TRIGGER一个存储到@sql2中，然后运行两个脚本(再次分别运行)。

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

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

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() function)。