*_Audit_Update trigger fails for GUID columns


I was getting the following error:
Msg 50000, Level 16, State 1, Procedure Product_Audit_Update, Line 460
error in [dbo].[Product_audit_update] trigger
Msg 3616, Level 16, State 1, Procedure ProductSave, Line 126
Transaction doomed in trigger. Batch has been aborted.
Turns out the following line of code was causing the problem:
AND isnull(Inserted.[guidColumn],'') <> isnull(Deleted.[guidColumn],'')
As as example:
declare @guid uniqueidentifier
declare @guid1 uniqueidentifier
set @guid=newid()
if (@guid <> isnull(@guid1,''))
    print 'this works'
Causes this error as well:
Msg 8169, Level 16, State 2, Line 6
Conversion failed when converting from a character string to uniqueidentifier.
I would suggest removing the isnull(,'') and allow the column values to compare by themselves.
AND Inserted.[guidColumn] <>Deleted.[guidColumn] 
Closed Aug 12, 2010 at 4:40 PM by PaulNielsenSQL


caderoux wrote Feb 10, 2009 at 10:19 PM

That will not work for NULLABLE columns (http://www.codeplex.com/AutoAudit/WorkItem/View.aspx?WorkItemId=21085), I just submitted a more general solution as part of that issue and did not realize this problem on the list was a symptom of the same ISNULL() usage.

wrote Feb 10, 2009 at 11:04 PM

wrote Aug 12, 2010 at 4:40 PM

wrote Aug 12, 2010 at 4:40 PM

wrote Feb 14, 2013 at 8:21 PM

wrote May 16, 2013 at 10:24 AM