Триггер SQL неправильно срабатывает при вставке нескольких строк

Я боролся с этим в течение нескольких дней. У меня есть табличный триггер, который создает запись аудита для каждого действия «Вставить», «Обновить» или «Удалить» и помещает ее в соответствующую таблицу аудита в соседней базе данных. Эта новая запись аудита содержит значение первичного ключа исходных таблиц, тип действия, моментальный снимок записи в формате XML, текущую дату и текущего пользователя.

Каждая из перечисленных выше порций информации присваивается\вычисляется из таблиц INSERTED\DELETED и присваивается значениям параметров, которые затем передаются в хранимую процедуру, выполняющую динамическую SQL-процедуру для ВСТАВКИ новой записи. Ради этого вопроса динамический SQL представляет собой простой оператор INSERT (показан ниже):

        INSERT INTO [340bAudit].[aud].[TableName]
            (
                RecordID
                ,ActionType
                ,xml_snapshot
                ,ModifiedDate
                ,ModifiedBy
            )
            VALUES (@RecordPK 
                ,@action
                ,@data 
                ,GETDATE() 
                ,SYSTEM_USER 
                   )

Этот триггер работает нормально, пока обновляется только запись, но как только возникает ситуация, когда несколько записей обновляются в одном выражении, я получаю следующее:

Подзапрос вернул более 1 значения. Это не разрешено, когда подзапрос следует за =, !=, ‹, ‹= , >, >= или когда подзапрос используется как выражение. Заявление было прекращено.

Триггер выглядит так:

ALTER TRIGGER [dbo].[RollOver_onUpdate] ON [dbo].[RollOver]
AFTER INSERT, update, DELETE
FOR EACH ROW

AS

BEGIN

/******** Audit *******/

DECLARE @TableName varchar(50)
        ,@RecordPK varchar(10)
        ,@action char(1)
        ,@data xml 

SET @TableName = 'RollOver'
SET @RecordPK = (SELECT DISTINCT RollOverID FROM INSERTED)

SET @action = 'I'; -- Set Action to Insert by default.
IF EXISTS (SELECT * FROM DELETED)
    BEGIN
        SET @action =
            CASE
                WHEN EXISTS (SELECT * FROM INSERTED)
                     THEN 'U' -- Set Action to Updated.
            ELSE 'D' -- Set Action to Deleted.       
            END
    END

SET @data = CASE WHEN @action <> 'D' THEN (SELECT'<rows>' + (SELECT * FROM INSERTED i FOR xml PATH) + '</rows>')
                 ELSE (SELECT'<rows>' + (SELECT * FROM DELETED d FOR xml PATH) + '</rows>')
            END

--Execute Audit Record Creation
EXECUTE sp_CreateAuditRecord    @Table = @TableName
                                ,@RecordID = @RecordPK
                                ,@ActionType = @action
                                ,@XML = @data


END

Могу ли я изменить этот триггер, чтобы он выполнялся построчно в этом случае? А если нет, то куда мне идти отсюда?

Я бы очень хотел сохранить гибкость динамического SQL, так как каждая таблица в моей базе данных использует одну и ту же логику аудита, и это первая и единственная таблица, которая, по-видимому, имеет проблему, которая, как я полагаю, связана с «использованием». ' этой таблицы.


tsql triggers dynamic-sql sql-insert
person SQLJax    schedule 20.08.2013    source источник
comment
Ознакомьтесь с рекомендациями: Кодирование триггеров SQL Server для многострочных операций blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/ сделать его основанным на наборе и избавиться от вызова proc   -  person SQLMenace    schedule 20.08.2013
comment
Спасибо, я посмотрю на это.   -  person SQLJax    schedule 20.08.2013
comment
Похоже, эта ссылка больше не работает. У кого есть альтернативная ссылка на аналогичный ресурс.   -  person SQLJax    schedule 30.08.2013

Ответы (1)


arrow_upward
0
arrow_downward

Это то, что я придумал. Это определенно не самый элегантный код, но он работает и решает мою проблему. Спасибо за ответы. Я бы проголосовал за одного, но никто не опубликовал ответ, который я мог бы принять.

/******** Audit *******/

DECLARE @action char(1)

SET @action = 'I'; -- Set Action to Insert by default.

        IF @@ROWCOUNT = 0
        RETURN

        IF EXISTS (SELECT * FROM DELETED)
            BEGIN
                SET @action =   CASE WHEN EXISTS (SELECT * FROM INSERTED)
                                        THEN 'U' -- Set Action to Updated.
                                    ELSE 'D' -- Set Action to Deleted.       
                                 END 

                INSERT INTO [340bAudit].[aud].[Orders]
                    (
                        RecordID
                        ,ActionType
                        ,xml_snapshot
                        ,ModifiedDate
                        ,ModifiedBy
                    )
                SELECT d.OrderID,
                       CASE WHEN EXISTS (SELECT * FROM INSERTED)
                                        THEN 'U' -- Set Action to Updated.
                                    ELSE 'D' -- Set Action to Deleted.       
                                 END ,
                       CASE WHEN @action <> 'D' THEN (SELECT'<rows>' + (SELECT * FROM INSERTED i FOR xml PATH) + '</rows>')
                            ELSE (SELECT'<rows>' + (SELECT * FROM DELETED d FOR xml PATH) + '</rows>') END,
                        GETDATE(), 
                        SYSTEM_USER 
                FROM DELETED d 
            END

        -- INSERTED Order Records
        INSERT INTO [340bAudit].[aud].[Orders]
            (
                RecordID
                ,ActionType
                ,xml_snapshot
                ,ModifiedDate
                ,ModifiedBy
            )
        SELECT i.OrderID,
                @action,
                (SELECT'<rows>' + (SELECT * FROM INSERTED i FOR xml PATH) + '</rows>'),
                GETDATE(), 
                SYSTEM_USER 
        FROM INSERTED i
person SQLJax    schedule 30.08.2013