Home » Generate Audit Table Trigger Code

Generate Audit Table Trigger Code

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

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.