sql-server-2008 – 动态sql错误：’CREATE TRIGGER’必须是查询

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

我试图生成一些动态的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,@objname;

END

CLOSE ObjectCursor ;
DEALLOCATE ObjectCursor ;

END

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

我删除了GO语句,因为这也是错误的.

我错过了什么？
为什么我使用EXEC(@sql)收到错误;甚至EXEC sp_executesql @sql;？
这与EXEC()中的上下文有关吗？
非常感谢任何帮助.

解决方法

另一方面,你不能将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’…’将语句作为动态查询运行.

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

可以使用第二个选项,但它需要额外的动态层(不确定它是否是一个单词).这是因为数据库名称是这里的一个参数,所以我们需要运行EXEC targetdatabase..sp_executesql N’…’作为动态脚本,因为要运行的实际脚本本身应该是一个动态脚本,因此它将嵌套两次.

那么,在(第二个)EXEC之前sp_executesql @sql; line添加以下内容：

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

如您所见,要将@sql的内容正确地集成为嵌套动态脚本,必须将它们括在单引号中.出于同样的原因,@ sql中的每个单引号都必须加倍(例如使用REPLACE() function,如上面的语句中所示).

（编辑：92站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!