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
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: