动态 sql 错误:'CREATE TRIGGER' 必须是查询批处理中的第一条语句 [英] dynamic sql error: 'CREATE TRIGGER' must be the first statement in a query batch

问题描述

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

As part of some administrative tasks, we have many tables that each need a trigger created. The trigger will set a flag and the date in the Audit database when an object has been modified. For simplicity, I have a table with all the objects that need triggers created.

我正在尝试为每个对象生成一些动态 sql 来执行此操作，但出现此错误:
'CREATE TRIGGER' 必须是查询批处理中的第一条语句.

I am trying to generate some dynamic sql to do this for each object, but I am getting this error:
'CREATE TRIGGER' must be the first statement in a query batch.

这里是生成sql的代码.

Here is the code to generate the 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 并将代码粘贴到新的查询窗口，则代码执行没有任何问题.

If I use PRINT and paste the code to a new query window, the code executes without any problem.

我删除了 GO 语句，因为这也会出错.

I have removed the GO statements as this was also giving errors.

我错过了什么?
为什么我在使用 EXEC(@sql); 甚至 EXEC sp_executesql @sql; 时出现错误?
这与 EXEC() 中的上下文有关吗?
非常感谢您的帮助.

What am I missing?
Why am I getting an error using EXEC(@sql); or even EXEC sp_executesql @sql;?
Is this something to do with the context within EXEC()?
Many thanks for any help.

推荐答案

如果您使用 SSMS(或其他类似工具)运行 此 脚本生成的代码，您将得到完全相同的错误.当您插入批处理分隔符 (GO) 时，它可以正常运行，但现在您不插入，您在 SSMS 中也会面临同样的问题.

If you use SSMS (or other similar tool) to run the code produced by this script, you will get exactly the same error. It could run all right when you inserted batch delimiters (GO), but now that you don't, you'll face the same issue in SSMS too.

另一方面，你不能把 GO 放在你的动态脚本中的原因是因为 GO 不是一个 SQL 语句，它只是一个被 SSMS 识别的分隔符和其他一些工具.可能您已经意识到这一点.

On the other hand, the reason why you cannot put GO in your dynamic scripts is because GO isn't a SQL statement, it's merely a delimiter recognised by SSMS and some other tools. Probably you are already aware of that.

无论如何，GO 的重点是让工具知道代码应该被拆分并且它的部分单独运行.而且，单独，也是您应该在代码中执行的操作.

Anyway, the point of GO is for the tool to know that the code should be split and its parts run separately. And that, separately, is what you should do in your code as well.

因此，您有以下选择:

• 在删除触发器的部分之后插入EXEC sp_execute @sql，然后重置@sql的值，然后存储和运行定义部分轮到它了；

• insert EXEC sp_execute @sql just after the part that drops the trigger, then reset the value of @sql to then store and run the definition part in its turn;

使用两个变量@sql1和@sql2，将IF EXISTS/DROP部分存入@sql1，即CREATE触发一个到 @sql2，然后运行两个脚本(再次，分别).

use two variables, @sql1 and @sql2, store the IF EXISTS/DROP part into @sql1, the CREATE TRIGGER one into @sql2, then run both scripts (again, separately).

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

But then, as you've already found out, you'll face another issue: you cannot create a trigger in another database without running the statement in the context of that database.

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

Now, there are 2 ways of providing the necessary context:

1) 使用 USE 语句；

2) 使用 EXEC targetdatabase..sp_executesql N'...' 将语句作为动态查询运行.

2) run the statement(s) as a dynamic query using EXEC targetdatabase..sp_executesql N'…'.

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

Obviously, the first option isn't going to work here: we cannot add USE … before CREATE TRIGGER, because the latter must be the only statement in the batch.

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

The second option can be used, but it will require an additional layer of dynamicity (not sure if it's a word). It's because the database name is a parameter here and so we need to run EXEC targetdatabase..sp_executesql N'…' as a dynamic script, and since the actual script to run is itself supposed to be a dynamic script, it, therefore, will be nested twice.

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

So, before the (second) EXEC sp_executesql @sql; line add the following:

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

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

As you can see, to integrate the contents of @sql as a nested dynamic script properly, they must be enclosed in single quotes. For the same reason, every single quotation mark in @sql must be doubled (e.g. using the REPLACE() function, as in the above statement).

这篇关于动态 sql 错误:'CREATE TRIGGER' 必须是查询批处理中的第一条语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！