Możesz użyć CHECKSUM()jako dość prostej metodologii porównywania rzeczywistych wartości, aby sprawdzić, czy zostały one zmienione. CHECKSUM()wygeneruje sumę kontrolną na liście przekazanych wartości, których liczba i typ są nieokreślone. Uważaj, istnieje niewielka szansa, że porównanie sum kontrolnych takich jak ta spowoduje fałszywe negatywy. Jeśli nie możesz sobie z tym poradzić, możesz HASHBYTESzamiast tego użyć 1 .
W poniższym przykładzie użyto AFTER UPDATEwyzwalacza, aby zachować historię zmian wprowadzonych w TriggerTesttabeli tylko w przypadku zmiany jednej z wartości w kolumnach Data1 lub Data2 . W przypadku Data3zmian nie są podejmowane żadne działania.
USE tempdb;
IF COALESCE(OBJECT_ID('dbo.TriggerTest'), 0) <> 0
BEGIN
DROP TABLE dbo.TriggerTest;
END
CREATE TABLE dbo.TriggerTest
(
TriggerTestID INT NOT NULL
CONSTRAINT PK_TriggerTest
PRIMARY KEY CLUSTERED
IDENTITY(1,1)
, Data1 VARCHAR(10) NULL
, Data2 VARCHAR(10) NOT NULL
, Data3 DATETIME NOT NULL
);
IF COALESCE(OBJECT_ID('dbo.TriggerResult'), 0) <> 0
BEGIN
DROP TABLE dbo.TriggerResult;
END
CREATE TABLE dbo.TriggerResult
(
TriggerTestID INT NOT NULL
, Data1OldVal VARCHAR(10) NULL
, Data1NewVal VARCHAR(10) NULL
, Data2OldVal VARCHAR(10) NULL
, Data2NewVal VARCHAR(10) NULL
);
GO
IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0
BEGIN
DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS
BEGIN
INSERT INTO TriggerResult
(
TriggerTestID
, Data1OldVal
, Data1NewVal
, Data2OldVal
, Data2NewVal
)
SELECT d.TriggerTestID
, d.Data1
, i.Data1
, d.Data2
, i.Data2
FROM inserted i
LEFT JOIN deleted d ON i.TriggerTestID = d.TriggerTestID
WHERE CHECKSUM(i.Data1, i.Data2) <> CHECKSUM(d.Data1, d.Data2);
END
GO
INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
VALUES ('blah', 'foo', GETDATE());
UPDATE dbo.TriggerTest
SET Data1 = 'blah', Data2 = 'fee'
WHERE TriggerTestID = 1;
SELECT *
FROM dbo.TriggerTest;
SELECT *
FROM dbo.TriggerResult
Jeśli nalegasz na użycie funkcji COLUMNS_UPDATED () , nie powinieneś sztywno kodować wartości porządkowej danych kolumn, ponieważ definicja tabeli może się zmienić, co może unieważnić wartości zakodowane na stałe . Możesz obliczyć, jaka powinna być wartość w czasie wykonywania, korzystając z tabel systemowych. Należy pamiętać, że COLUMNS_UPDATED()funkcja zwraca wartość true dla danego bitu kolumny, jeśli kolumna jest modyfikowana w DOWOLNYM wierszu, na który wpływa UPDATE TABLEinstrukcja.
USE tempdb;
IF COALESCE(OBJECT_ID('dbo.TriggerTest'), 0) <> 0
BEGIN
DROP TABLE dbo.TriggerTest;
END
CREATE TABLE dbo.TriggerTest
(
TriggerTestID INT NOT NULL
CONSTRAINT PK_TriggerTest
PRIMARY KEY CLUSTERED
IDENTITY(1,1)
, Data1 VARCHAR(10) NULL
, Data2 VARCHAR(10) NOT NULL
, Data3 DATETIME NOT NULL
);
IF COALESCE(OBJECT_ID('dbo.TriggerResult'), 0) <> 0
BEGIN
DROP TABLE dbo.TriggerResult;
END
CREATE TABLE dbo.TriggerResult
(
TriggerTestID INT NOT NULL
, Data1OldVal VARCHAR(10) NULL
, Data1NewVal VARCHAR(10) NULL
, Data2OldVal VARCHAR(10) NULL
, Data2NewVal VARCHAR(10) NULL
);
GO
IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0
BEGIN
DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS
BEGIN
DECLARE @ColumnOrdinalTotal INT = 0;
SELECT @ColumnOrdinalTotal = @ColumnOrdinalTotal
+ POWER (
2
, COLUMNPROPERTY(t.object_id,c.name,'ColumnID') - 1
)
FROM sys.schemas s
INNER JOIN sys.tables t ON s.schema_id = t.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE s.name = 'dbo'
AND t.name = 'TriggerTest'
AND c.name IN (
'Data1'
, 'Data2'
);
IF (COLUMNS_UPDATED() & @ColumnOrdinalTotal) > 0
BEGIN
INSERT INTO TriggerResult
(
TriggerTestID
, Data1OldVal
, Data1NewVal
, Data2OldVal
, Data2NewVal
)
SELECT d.TriggerTestID
, d.Data1
, i.Data1
, d.Data2
, i.Data2
FROM inserted i
LEFT JOIN deleted d ON i.TriggerTestID = d.TriggerTestID;
END
END
GO
--this won't result in rows being inserted into the history table
INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
VALUES ('blah', 'foo', GETDATE());
SELECT *
FROM dbo.TriggerResult;
--this will insert rows into the history table
UPDATE dbo.TriggerTest
SET Data1 = 'blah', Data2 = 'fee'
WHERE TriggerTestID = 1;
SELECT *
FROM dbo.TriggerTest;
SELECT *
FROM dbo.TriggerResult;
--this WON'T insert rows into the history table
UPDATE dbo.TriggerTest
SET Data3 = GETDATE()
WHERE TriggerTestID = 1;
SELECT *
FROM dbo.TriggerTest;
SELECT *
FROM dbo.TriggerResult
--this will insert rows into the history table, even though only
--one of the columns was updated
UPDATE dbo.TriggerTest
SET Data1 = 'blum'
WHERE TriggerTestID = 1;
SELECT *
FROM dbo.TriggerTest;
SELECT *
FROM dbo.TriggerResult;
To demo wstawia wiersze do tabeli historii, których być może nie należy wstawiać. W wierszach Data1zaktualizowano kolumnę dla niektórych wierszy i Data3zaktualizowano kolumnę dla niektórych wierszy. Ponieważ jest to pojedyncza instrukcja, wszystkie wiersze są przetwarzane przez pojedyncze przejście przez wyzwalacz. Ponieważ niektóre wiersze zostały Data1zaktualizowane, co stanowi część COLUMNS_UPDATED()porównania, wszystkie wiersze widoczne dla wyzwalacza są wstawiane do TriggerHistorytabeli. Jeśli jest to „niepoprawne” w twoim scenariuszu, być może będziesz musiał obsługiwać każdy wiersz osobno, używając kursora.
INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
SELECT TOP(10) LEFT(o.name, 10)
, LEFT(o1.name, 10)
, GETDATE()
FROM sys.objects o
, sys.objects o1;
UPDATE dbo.TriggerTest
SET Data1 = CASE WHEN TriggerTestID % 6 = 1 THEN Data2 ELSE Data1 END
, Data3 = CASE WHEN TriggerTestID % 6 = 2 THEN GETDATE() ELSE Data3 END;
SELECT *
FROM dbo.TriggerTest;
SELECT *
FROM dbo.TriggerResult;
TriggerResultTabela ma teraz jakieś potencjalnie mylących wiersze, które wyglądają tak, jakby nie należą, ponieważ pokazują one absolutnie żadnych zmian (w dwóch kolumnach w tej tabeli). W drugim zestawie wierszy na poniższym obrazku TriggerTestID 7 jest jedynym, który wygląda na zmodyfikowany. W pozostałych wierszach Data3zaktualizowano tylko kolumnę; jednak ponieważ zaktualizowano jeden wiersz w partii Data1, wszystkie wiersze są wstawiane do TriggerResulttabeli.
Alternatywnie, jako @AaronBertrand i @srutzky wskazał, można przeprowadzić porównanie rzeczywistych danych w insertedi deletedwirtualnych stołach. Ponieważ struktura obu tabel jest identyczna, możesz użyć EXCEPTklauzuli w wyzwalaczu, aby przechwycić wiersze, w których zmieniły się precyzyjne kolumny:
IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0
BEGIN
DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS
BEGIN
;WITH src AS
(
SELECT d.TriggerTestID
, d.Data1
, d.Data2
FROM deleted d
EXCEPT
SELECT i.TriggerTestID
, i.Data1
, i.Data2
FROM inserted i
)
INSERT INTO dbo.TriggerResult
(
TriggerTestID,
Data1OldVal,
Data1NewVal,
Data2OldVal,
Data2NewVal
)
SELECT i.TriggerTestID
, d.Data1
, i.Data1
, d.Data2
, i.Data2
FROM inserted i
INNER JOIN deleted d ON i.TriggerTestID = d.TriggerTestID
END
GO
1 - patrz /programming/297960/hash-collision-what-are-the-ances szanse na omówienie znikomej szansy, że obliczenia HASHBYTES mogą również powodować kolizje. Preshing ma również przyzwoitą analizę tego problemu.
SETliście, czy też wartości rzeczywiście się zmieniły? ZarównoUPDATEiCOLUMNS_UPDATED()tylko ci powiem pierwszy. Jeśli chcesz wiedzieć, czy wartości rzeczywiście się zmieniły, musisz dokonać właściwego porównaniainsertedideleted.