开发者

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.*)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜