Функция обновления в триггере TSQL

У меня вопрос о функции TSQL Update. Например, у меня есть таблица с полем Name. Если я проверю, изменилось ли имя поля или нет в триггере After Update, это выглядит следующим образом:

  if Update(Name)
  Begin
    -- process
  End

Будет ли Update все еще возвращать TRUE, даже если имя не изменено? Следующий оператор обновления обновит его с тем же значением:

  SELECT @v_Name = Name From MyTable Where Id = 1;
  Update MyTable Set Name = @v_Name where Id = 1;

Если Update() возвращает TRUE, даже значение Name не изменяется, мне нужно сравнить значение во вставленных и удаленных виртуальных таблицах, чтобы узнать, действительно ли значение действительно изменилось?

Кстати, вставленные и удаленные являются виртуальными таблицами, и они могут содержать более одного ряда данных, если несколько строк данных изменяются одним инструкцией TSQL INSERT или UPDATE. В случае более чем одной записи количество номеров строк в вставленных и удаленных виртуальных таблицах одинаково и каково истинное значение Update (Name) как TRUE? Означает ли это, что по крайней мере один из них изменен? Или Update (Name) означает, что поле Name было задано оператором Update независимо от того, изменилось ли значение?

Я использую SQL-сервер Microsoft SQL 2005.

Ответы

Ответ 1

UPDATE() может быть правдой, даже если это то же значение. Я бы не стал полагаться на него лично и сравнивал бы ценности.

Во-вторых, DELETED и INSERTED имеют одинаковое количество строк.

Функция Update() не для каждой строки, а для всех строк. Другая причина не использовать его.

Подробнее здесь, в MSDN, однако это немного разреженный, действительно.

После комментария:

IF EXISTS (
    SELECT
        *
    FROM
        INSERTED I
        JOIN
        DELETED D ON I.key = D.key
    WHERE
        D.valuecol <> I.valuecol --watch for NULLs!
    )
   blah

Ответ 2

Триггеры сложны, и вам нужно думать навалом, когда вы их создаете. Триггер срабатывает один раз для каждого оператора UPDATE. Если этот оператор UPDATE обновляет несколько строк, триггер по-прежнему будет запускать только один раз. Функция UPDATE() возвращает значение true для столбца, если этот столбец включен в оператор UPDATE. Эта функция помогает повысить эффективность триггеров, позволяя обойти SQL-логику, когда этот столбец даже не включен в инструкцию обновления. Он не говорит вам, изменилось ли значение для столбца в данной строке.

Вот пример таблицы...

CREATE TABLE tblSample
(
    SampleID INT PRIMARY KEY,
    SampleName VARCHAR(10),
    SampleNameLastChangedDateTime DATETIME,
    Parent_SampleID INT
)

Если для этой таблицы использовался следующий SQL:

UPDATE tblSample SET SampleName = 'hello'

.. и триггер AFTER INSERT, UPDATE, этот конкретный оператор SQL всегда будет оценивать функцию UPDATE следующим образом:

IF UPDATE(SampleName) --aways evaluates to TRUE
IF UPDATE(SampleID)  --aways evaluates to FALSE
IF UPDATE(Parent_SampleID) --aways evaluates to FALSE

Обратите внимание, что UPDATE (SampleName) всегда будет истинным для этого оператора SQL, независимо от того, какие значения SampleName были раньше. Он возвращает true, потому что оператор UPDATE включает столбец SampleName в разделе SET этого предложения и не зависит от того, какие значения были до или после. Функция UPDATE() не определяет, изменились ли значения. Если вы хотите выполнять действия на основе изменения значений, вам понадобится использовать SQL и сравнить вставленные и удаленные строки.

Здесь приведен подход к синхронизации последнего обновленного столбца:

--/*
IF OBJECT_ID('dbo.tgr_tblSample_InsertUpdate', 'TR') IS NOT NULL 
  DROP TRIGGER dbo.tgr_tblSample_InsertUpdate
GO
--*/

CREATE TRIGGER dbo.tgr_tblSample_InsertUpdate ON dbo.tblSample
  AFTER INSERT, UPDATE 
AS
BEGIN --Trigger

  IF UPDATE(SampleName)  
    BEGIN
      UPDATE tblSample SET
      SampleNameLastChangedDateTime = CURRENT_TIMESTAMP
      WHERE
        SampleID IN (SELECT Inserted.SampleID 
               FROM Inserted LEFT JOIN Deleted ON Inserted.SampleID = Deleted.SampleID
               WHERE COALESCE(Inserted.SampleName, '') <> COALESCE(Deleted.SampleName, ''))
    END

END --Trigger

Логика для определения, была ли обновлена ​​строка, находится в предложении WHERE выше. Это реальная проверка, которую вам нужно сделать. Моя логика использует COALESCE для обработки значений NULL и INSERTS.

...
WHERE
  SampleID IN (SELECT Inserted.SampleID 
               FROM Inserted LEFT JOIN Deleted ON Inserted.SampleID = Deleted.SampleID
               WHERE COALESCE(Inserted.SampleName, '') <> COALESCE(Deleted.SampleName, ''))

Обратите внимание, что проверка IF UPDATE() используется для повышения эффективности запуска, когда столбец SampleName НЕ обновляется. Если SQL-запрос обновил столбец Parent_SampleID, то проверка IF UPDATE (SampleName) поможет обойти более сложную логику в этом IF-заявлении, когда ему не нужно запускать. Рассмотрите возможность использования UPDATE(), когда это необходимо, но не по неправильной причине.

Также понимайте, что в зависимости от вашей архитектуры функция UPDATE может вам не пригодиться. Если ваша архитектура кода использует средний уровень, который всегда обновляет все столбцы в строке таблицы со значениями в бизнес-объекте при сохранении объекта, функция UPDATE() в триггере становится бесполезной. В этом случае ваш код, вероятно, всегда обновляет все столбцы с каждым выражением UPDATE, выпущенным из среднего уровня. В этом случае функция UPDATE (columnname) всегда будет оценивать значение true, когда ваши бизнес-объекты будут сохранены, потому что все имена столбцов всегда включены в операторы обновления. В этом случае было бы нецелесообразно использовать UPDATE() в триггере, и в большинстве случаев это будет лишние накладные расходы в течение этого времени.

Здесь некоторые SQL, чтобы играть с триггером выше:

INSERT INTO tblSample
(
  SampleID,
  SampleName
)
SELECT 1, 'One'
UNION SELECT 2, 'Two'
UNION SELECT 3, 'Three'

GO
SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample

/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       One    2010-10-27 14:52:42.567
2       Two    2010-10-27 14:52:42.567
3       Three  2010-10-27 14:52:42.567
*/

GO

INSERT INTO tblSample
(
  SampleID,
  SampleName
)
SELECT 4, 'Foo'
UNION SELECT 5, 'Five'

GO
SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample
/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       One    2010-10-27 14:52:42.567
2       Two    2010-10-27 14:52:42.567
3       Three  2010-10-27 14:52:42.567
4       Foo    2010-10-27 14:52:42.587
5       Five   2010-10-27 14:52:42.587
*/

GO

UPDATE tblSample SET SampleName = 'Foo' 

SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample 
/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       Foo    2010-10-27 14:52:42.657
2       Foo    2010-10-27 14:52:42.657
3       Foo    2010-10-27 14:52:42.657
4       Foo    2010-10-27 14:52:42.587
5       Foo    2010-10-27 14:52:42.657
*/
GO

UPDATE tblSample SET SampleName = 'Not Prime' WHERE SampleID IN (1,4)

SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample
/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       Not Prime  2010-10-27 14:52:42.680
2       Foo        2010-10-27 14:52:42.657
3       Foo        2010-10-27 14:52:42.657
4       Not Prime  2010-10-27 14:52:42.680
5       Foo        2010-10-27 14:52:42.657
*/

--Clean up...
DROP TRIGGER dbo.tgr_tblSample_InsertUpdate
DROP TABLE tblSample

Пользователь GBN предложил следующее:

IF EXISTS (
    SELECT
        *
    FROM
        INSERTED I
        JOIN
        DELETED D ON I.key = D.key
    WHERE
        D.valuecol <> I.valuecol --watch for NULLs!
    )
   blah

Предложение GBN использовать IF (EXISTS (...) и поставить логику в этом выражении IF, если строки существуют, которые были изменены, могут работать. Этот подход будет срабатывать для ВСЕХ строк, включенных в триггер, даже если только некоторые из строки были фактически изменены (что может быть подходящим для вашего решения, но также может оказаться неприемлемым, если вы хотите что-то сделать только для строк, в которых значения были изменены.) Если вам нужно что-то сделать для строк, в которых произошло фактическое изменение, вы нужна другая логика в SQL, которую он предоставил.

В моих примерах выше, когда выдается инструкция UPDATE tblSample SET SampleName = 'Foo', а четвертая строка уже "foo", использование GBN-подхода для обновления столбца "последнее измененное время" также обновит четвертую строку, что в данном случае было бы неуместным.

Ответ 3

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

Вот трюк, который я встретил в некотором коде, который я поддерживал (не знаю оригинального автора): Используйте UNION и GROUP BY с предложением HAVING, чтобы определить, какие столбцы были изменены.

например, в триггере, чтобы получить идентификатор строк, которые были изменены:

SELECT SampleID
FROM 
    (
        SELECT SampleID, SampleName
        FROM deleted

        -- NOTE: UNION, not UNION ALL.  UNION by itself removes duplicate 
        --  rows.  UNION ALL includes duplicate rows.
        UNION 

        SELECT SampleID, SampleName
        FROM inserted
    ) x
GROUP BY SampleID
HAVING COUNT(*) > 1

Это слишком большая работа, когда вы проверяете, изменился ли только один столбец. Но если вы проверяете 10 или 20 столбцов, метод UNION работает намного меньше, чем

WHERE COALESCE(Inserted.Column1, '') <> COALESCE(Deleted.Column1, '')
    OR COALESCE(Inserted.Column2, '') <> COALESCE(Deleted.Column2, '')
    OR COALESCE(Inserted.Column3, '') <> COALESCE(Deleted.Column3, '')
    OR ...

Ответ 4

Я думаю, что следующий код лучше, чем приведенные выше примеры, поскольку он фокусируется только на столбцах, которые вы хотите проверить в сжатой и эффективной форме.

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

Он использует оператор набора EXCEPT для возврата любых строк из левого запроса, которые также не найдены в правильном запросе. Этот код можно использовать в триггерах INSERT и UPDATE.

Столбец "PrimaryKeyID" является первичным ключом таблицы (может быть несколькими столбцами) и необходим для согласования между двумя наборами.

-- Only do trigger logic if specific field values change.
IF EXISTS(SELECT  PrimaryKeyID
                ,Column1
                ,Column7
                ,Column10
          FROM inserted
          EXCEPT
          SELECT PrimaryKeyID
                ,Column1
                ,Column7
                ,Column10
          FROM deleted )    -- Tests for modifications to fields that we are interested in
BEGIN
          -- Put code here that does the work in the trigger

END

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

Надеюсь, это интересно: -)

Ответ 5

Триггер обновления запускается во всех операциях обновления. удаленные строки доступны в триггере в "вставленных" и "удаленных" таблицах. Вы можете сравнить старые и новые значения, сравнив столбцы PK в двух таблицах (если у вас есть ПК). Фактическая таблица остается неизменной до тех пор, пока триггер не завершит выполнение.