Problem with IF UPDATE(colname) in trigger

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 1

The UPDATE function will return true if that column was included in the SET clause of the UPDATE statement even if the value does not change.

Your trigger will only work with singleton updates try this

update temptbl

set col3 = col3 + 'x'

FROM inserted i

join deleted on i.col1 = d.col1 AND ISNULL(i.col2,-1) <> ISNULL(d.col2,-1)

where temptbl.col1 = i.col1

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

Paul Corbin Old Hand Points: 361 More actions · Answer 2

Thank you! Thank you! THANK YOU!

With just a little tweaking it works just

the way it did in SQL6.5.

Here's how I rewrote it. You can see the

changes were very small.

update temptbl

set col3 = i.col3 + 'x'

FROM inserted i

join deleted d on i.col1 = d.col1 AND ISNULL(i.col2,-1) != ISNULL(d.col2,-1)

where temptbl.col1 = i.col1

THANK YOU again!

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 3

Also be aware that col3 + 'x' will be NULL if col3 is NULL unlike in 6.5 where it would be 'x', you might want to include ISNULL(col3) + 'x'

Simon Sabin

Co-author of SQL Server 2000 XML Distilled