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,如上面的语句中所示).

（编辑：湖南网）

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