This code will generate code to do a column-by-column comparison against a table, assuming that you are joining the inserted table (aliased as “i”) with the deleted table (aliased as “d”) in a trigger:
SET NOCOUNT ONDECLARE @x sysnameSELECT @x = 'authors'CREATE TABLE #x(SQL varchar(255), colid int )INSERT #xselect 'OR IsNull( i.' + name + ', "12/31/9999" ) <> IsNull( d.' + name + ',"12/31/9999" )' , colidfrom syscolumnswhere id = object_id(@x)and status <> 0AND name <> 'timestamp'AND usertype = 12unionselect 'OR IsNull( i.' + name + ', -9999) <> IsNull( d.' + name + ', -9999)' , colidfrom syscolumnswhere id = object_id(@x)and status <> 0AND name <> 'timestamp'and usertype = 8unionselect 'OR IsNull( i.' + name + ', "" ) <> IsNull( d.' + name + ', "" )' , colidfrom syscolumnswhere id = object_id(@x)and status <> 0AND name <> 'timestamp'and scale IS NULLAND usertype NOT IN ( 12, 8 )unionselect 'OR IsNull( i.' + name + ', 0) <> IsNull( d.' + name + ', 0)' , colidfrom syscolumnswhere id = object_id(@x)and status <> 0AND name <> 'timestamp'and prec = 3AND scale = 0AND usertype NOT IN ( 12, 8 )unionselect 'OR IsNull( i.' + name + ', -9999) <> IsNull( d.' + name + ', -9999)' , colidfrom syscolumnswhere id = object_id(@x)and status <> 0AND name <> 'timestamp'and scale IS NOT NULLand prec <> 3AND usertype NOT IN ( 12, 8 )unionselect 'OR i.' + name + ' <> d.' + name , colidfrom syscolumnswhere id = object_id(@x)and status = 0AND name <> 'timestamp'ORDER BY 2SELECT " " + SQLFROM #xDROP TABLE #XSET NOCOUNT OFFGO
Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
