Dynamic Audit Trigger

• Brian Stanek

  Mr or Mrs. 500

  Points: 548

  More actions

  March 4, 2004 at 9:59 am

  #497347

  Actually, the number of transactions we have is high, but this is the simplest solution considering the number of interfaces we have into our tables.  And, luckly the update statements are all single row and not multi.

• Brian Stanek

  Mr or Mrs. 500

  Points: 548

  More actions

  March 4, 2004 at 10:10 am

  #497349

  why that many temp tables?  can't you just

    set @stmt = 'select @value = cast(' + @colname + '  as varchar(4000)) from inserted'

    exec sp_executesql @stmt, '@value varchar(4000) OUTPUT', @value OUTPUT

   set @stmt = 'select @oldvalue = cast(' + @colname + '  as varchar(4000)) from deleted'

    exec sp_executesql @stmt, '@oldvalue  varchar(4000) OUTPUT', @oldvalue OUTPUT

  ---

  I had to use the temp  tables, because when you run those statements you receive the error message:

  Server: Msg 208, Level 16, State 1, Line 1

  Invalid object name 'inserted'.

   

  sp_executesql runs in a different space than the trigger and while it can pick up the temporary tables, it cannot pick up the virtual tables inserted/deleted

• noeld

  SSC Guru

  Points: 96590

  More actions

  March 4, 2004 at 11:08 am

  #497358

  you are right. But I was showing that you didn't need to insert in a temp table just to get the value of the column!

  so:

  set @stmt = 'select @value = cast(' + @colname + '  as varchar(4000)) from #tempins'

    exec sp_executesql @stmt, '@value varchar(4000) OUTPUT', @value OUTPUT

   set @stmt = 'select @oldvalue = cast(' + @colname + '  as varchar(4000)) from #tempdel'

    exec sp_executesql @stmt, '@oldvalue  varchar(4000) OUTPUT', @oldvalue OUTPUT

  seems better than:

          create table #temp (x nvarchar(4000))

          set @stmt = 'insert into #temp select ' + @colname + ' from #tempins'

          EXEC sp_executesql @stmt

          select @value = x from #temp

          set @stmt = 'insert into #temp select ' + @colname + ' from #tempdel'

          EXEC sp_executesql @stmt

          select @oldvalue = x from #temp

          drop table #temp

   

  
  * Noel

• well0549

  SSCrazy

  Points: 2259

  More actions

  September 27, 2004 at 9:10 am

  #524533

  Maybe a little late but I contributed a script wich will audit any table.....

  It looks if a specificv audit table is present, if it isn't it will create a (audit_) duplicate of the original table and create a trigger that wil record any change to the table in the audit table

  I hope somebody finds it usefull.....