动态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.

我缺少什么？

为什么使用<$ c会出错$ c> 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（或其他类似工具）运行由 this 脚本生成的代码，则会得到完全相同的错误。当您插入批量定界符（ 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 ，将触发器创建到 @ 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 目标etdatabase .. sp_executesql N'...'。

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

显然，第一种选择在这里行不通：我们不能在创建触发器之前添加 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.

可以使用第二个选项 ，但这需要附加一层 dynamic （不确定是否是单词）。这是因为数据库名称是此处的参数，因此我们需要运行 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屋！