Вопрос по sql-server-2008 – динамическая ошибка sql: «CREATE TRIGGER» должен быть первым оператором в пакете запроса

7

В рамках некоторых административных задач у нас есть много таблиц, каждая из которых нуждается в создании триггера. Триггер установит флаг и дату в базе данных аудита, когда объект был изменен. Для простоты у меня есть таблица со всеми объектами, для которых нужны триггеры.

Я пытаюсь создать динамический 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_'[email protected]+'_AuditObjectUpdates]'')) '
    SET @sql = @sql + N'BEGIN DROP TRIGGER '+QUOTENAME(@schemaname)+'.[Tiud_'[email protected]+'_AuditObjectUpdates]; END; '[email protected]
    SET @sql = @sql + N'CREATE TRIGGER '+QUOTENAME(@schemaname)+'.[Tiud_'[email protected]+'_AuditObjectUpdates] '[email protected]
    SET @sql = @sql + N'   ON '+QUOTENAME(@schemaname)+'.['[email protected]+'] '[email protected]
    SET @sql = @sql + N'   AFTER INSERT,DELETE,UPDATE'[email protected]
    SET @sql = @sql + N'AS '[email protected]
    SET @sql = @sql + N'IF EXISTS(SELECT * FROM Audit.dbo.ObjectUpdates WHERE DatabaseName = '''[email protected]+''' AND ObjectName = '''[email protected]+''' AND RequiresUpdate=0'[email protected]
    SET @sql = @sql + N'BEGIN'[email protected]
    SET @sql = @sql + N'    SET NOCOUNT ON;'[email protected]
    SET @sql = @sql + N'    UPDATE  Audit.dbo.ObjectUpdates'[email protected]
    SET @sql = @sql + N'    SET RequiresUpdate = 1'[email protected]
    SET @sql = @sql + N'    WHERE   DatabaseName = '''[email protected]+''' '[email protected]
    SET @sql = @sql + N'        AND ObjectName = '''[email protected]+''' '[email protected]

    SET @sql = @sql + N'END' [email protected]
    SET @sql = @sql + N'ELSE' [email protected]
    SET @sql = @sql + N'BEGIN' [email protected]
    SET @sql = @sql + N'    SET NOCOUNT ON;' [email protected]
    SET @sql = @sql + @CRLF
    SET @sql = @sql + N'    -- Update ''SourceLastUpdated'' date.'[email protected]
    SET @sql = @sql + N'    UPDATE  Audit.dbo.ObjectUpdates'[email protected]
    SET @sql = @sql + N'    SET SourceLastUpdated = GETDATE() '[email protected]
    SET @sql = @sql + N'    WHERE   DatabaseName = '''[email protected]+''' '[email protected]
    SET @sql = @sql + N'        AND ObjectName = '''[email protected]+''' '[email protected]
    SET @sql = @sql + N'END; '[email protected]

    --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()?
Большое спасибо за любую помощь.

Ваш Ответ

2   ответа
0

создание триггера должно быть выполнено в его собственном пакете выполнения. Вы находитесь внутри процедуры, поэтому вы не сможете ее создать.

Я предлагаю добавить @sql во временную таблицу, а затем, когда процесс завершит генерацию всех операторов, зациклить эту временную таблицу для их выполнения и создать триггеры.

17

Если вы используете SSMS (или другой подобный инструмент) для запуска кода, созданногоthis скрипт, вы получите точно такую же ошибку. Он может работать нормально, когда вы вставили разделители пакетов (GO), но теперь, когда вы этого не сделаете, вы столкнетесь с той же проблемой в SSMS.

С другой стороны, причина, почему вы не можете поставитьGO в ваших динамических скриптах, потому чтоGO это не оператор SQL, это просто разделитель, распознаваемый SSMS и некоторыми другими инструментами. Возможно, вы уже знаете об этом.

Во всяком случае, точкаGO инструмент должен знать, что код должен быть разбит и его части выполняютсяseparately, И это,separatelyэто то, что вы должны делать в своем коде.

Итак, у вас есть эти варианты:

  • 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;

  • 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).

Но тогда, как вы уже узнали, вы столкнетесь с другой проблемой: вы не можете создать триггер в другой базе данных без выполнения оператораin the context of that database.

Теперь есть 2 способа предоставления необходимого контекста:

1) использоватьUSE заявление;

2) выполнить оператор (ы) как динамический запрос, используяEXEC targetdatabase..sp_executesql N'…'.

Очевидно, что первый вариант здесь не сработает: мы не можем добавитьUSE … доCREATE TRIGGERпотому что последний должен быть единственным оператором в пакете.

Второй вариантcan будет использоваться, но это потребует дополнительного слояdynamicity (не уверен, что это слово). Это потому, что имя базы данных здесь является параметром, и поэтому нам нужно запуститьEXEC targetdatabase..sp_executesql N'…' as динамический сценарий, и поскольку фактический сценарий, который должен быть запущен, сам по себе должен быть динамическим, он, следовательно, будет вложен дважды

Итак, до (второй)EXEC sp_executesql @sql; В строку добавьте следующее:

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

Как видите, для интеграции содержимого@sql как правильно вложенный динамический скрипт, они должны быть заключены в одинарные кавычки. По той же причине каждая кавычкаin @sql должно быть удвоено (например, используяREPLACE() function, как в приведенном выше утверждении).

Это отлично, совершенно ясно и именно то, что я хотел. Единственная проблема - я проверил в BOL - была сQUOTENAMEгде строковый параметр ограничен 128 символами. Входные данные более 128 символов возвращаютсяNULL, поэтому я поместил в кавычки вместо использования функции. Теперь я вижу, как'EXEC'[email protected]+'..sp_executesql' может быть чрезвычайно мощным и позволяет выполнять динамический sql в другом контексте / базе данных. Еще раз большое спасибо за вашу помощь. MarkusBee
Большое спасибо за это. Теперь я разделил код на две части. как вы предлагаете в первом варианте выше, следующим образом: MarkusBee
@markb: Ах, действительно, мой плохой. Вы можете заменить функциональностьQUOTENAME с чем-то вроде'''' + REPLACE(@sql, '''', '''''') + '''', (Т.е. я думаю, что недостаточно просто приложить@sql в кавычках, но поскольку у вас есть строковые выражения внутри определения триггера, вам также нужно будет удваивать каждую кавычку там.)
@markb: пожалуйста, смотрите мое обновление. Я не уверен, что все так ясно, как мне хотелось бы, поэтому, пожалуйста, не стесняйтесь спрашивать.
[РЕДАКТИРОВАТЬ время предыдущего комментария.] Большое спасибо. Я разделил код на две части. как вы предлагаете в первом варианте. Первая часть исполняется отлично. Я поясню, что процедура выполняется из «Аудита». база данных и объекты, которые требуют триггеров, находятся в других базах данных. ИсключаяCREATE TRIGGER Теперь оператор генерирует следующую ошибку, даже если используется полное имя таблицы: & quot; Невозможно создать триггер для [...], так как цель отсутствует в текущей базе данных. & quot; Есть ли способ обойти это? Как я могу заставить его выполняться в контексте другой базы данных? Благодарю. MarkusBee

Похожие вопросы