5

Closed

*_Audit_Update trigger fails for GUID columns

description

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] 
 
Shane.
Closed Aug 12, 2010 at 4:40 PM by PaulNielsenSQL
fixed

comments

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.