Compare two records in two table in trigger sql server 2005
i want that if DELETED and INSERTED table have the same record then i will not update a another table. so please tell me how can i compare two rows in two different table DELETED and INSER开发者_如何学GoTED having same structure.
please help me with sample code.
thanks
You have to test each and every column
INSERT OtherTable (keycol, col1, col2, ...)
SELECT
I.keycol, I.Col1, I.Col2, ..
FROM
INSERTED I
JOIN
DELETED D On I.keycol = D.keycol
WHERE
I.col1 <> I.col1
OR
I.col2 <> I.col2
OR
ISNULL(I.col3, 0) <> ISNULL(I.col3, 0) --nullable columns
OR
...
You can also use HASHBYTES
INSERT ...
SELECT
I.keycol, I.Col1, I.Col2, ..
FROM
INSERTED I
JOIN
DELETED D On I.keycol = D.keycol
WHERE
HashBytes('SHA1', CAST(I.col1 AS nvarchar) + I.col2 + etc)
<>
HashBytes('SHA1', CAST(D.col1 AS nvarchar) + D.col2 + etc)
Not 100% reliable, use CHECKSUM
INSERT ...
SELECT
I.keycol, I.Col1, I.Col2, ..
FROM
INSERTED I
JOIN
DELETED D On I.keycol = D.keycol
WHERE
CHECKSUM(I.*) <> CHECKSUM(D.*)
精彩评论