Logging Table Updates

• cobra429

  SSC Veteran

  Points: 204

  More actions

  October 2, 2002 at 10:56 am

  #79137

  I have created a trigger that will track table updates on a column by column basis using the Columns_Updated() function. The problem I am having is the trigger is firing 8 times per update to the table. Any suggestions on what is wrong will be greatly appreciated.

  The Trigger code is as follows:

  CREATE trigger trA_Cust_LDCACCT_TB_U

  ---list all columns that were changed

  on dbo.A_CUST_LDCACCT_TB

  After Update ----- For Inserts

  As

  Set NoCount on

  declare @Col int ,

  @exp int ,

  @byte int ,

  @power int,

  @intColumn int,

  @intCountColumn int,

  @chvQuery varchar(999),

  @ColumnName varchar(999),

  @ColumnUpdated varchar(1)

  ----Select The information from the Deleted and Inserted Tables to temp tables

  Select * Into #tmp_Deleted FROM Deleted

  Select * Into #tmp_Inserted FROM Inserted

  ----Count the Columns in the Table

  Select @intCountColumn = Count(Ordinal_Position)

  From Information_Schema.Columns

  Where Table_Name = 'A_CUST_LDCACCT_TB'

  Select @intColumn = 1

  Print '-----You Are Entering the Loop-------'

  -----Loop through the columns

  While @intColumn <= @intCountColumn

  begin

  Select @Col = @intColumn

  Select @exp = ( @Col%8 ) + ( -1*( sign( @Col % 8 ) - 1 ) * 8 ) - 1

  Select @byte = ( @Col / 8 ) + ( sign( @Col % 8 ) - 1 ) + 1

  Print 'Column No ' + Convert(varchar(3), @col)

  Print '@exp = ' + Convert(varchar(3), @exp)

  Print ' @byte = ' + Convert(varchar(3),@byte)

  if substring( columns_updated() , @byte , 1 ) & power( 2, @exp ) > 0

  Begin

  Select @ColumnName = Convert(varchar,Col_Name(OBJECT_ID('A_CUST_LDCACCT_TB'), @intColumn))

  Print 'Column ('

  + Cast(@intColumn as varchar)

  + ') '

  + Col_Name(OBJECT_ID('A_CUST_LDCACCT_TB'), @intColumn)

  + ' has been changed!'

  Exec(@chvQuery)

  --Print 'The Column Update = ' + @ColumnUpdated

  Begin

  Select @chvQuery = 'Insert Into A_CUST_LDCACCT_HISTORY_TB '

  Select @chvQuery = @chvQuery + '(SERV_ACCT_NO, '

  Select @chvQuery = @chvQuery + 'COLUMN_NAME, '

  Select @chvQuery = @chvQuery + 'OLD_VALUE, '

  Select @chvQuery = @chvQuery + 'NEW_VALUE, '

  Select @chvQuery = @chvQuery + 'CREATE_DT, '

  Select @chvQuery = @chvQuery + 'CREATE_USR) '

  Select @chvQuery = @chvQuery + 'Select i.SERV_ACCT_NO, ' + char(39) + @ColumnName + char(39) + ', '

  Select @chvQuery = @chvQuery + 'd.' + @ColumnName + ', '

  Select @chvQuery = @chvQuery + 'i.' + @ColumnName + ', '

  Select @chvQuery = @chvQuery + 'i.Create_DT' + ', '

  Select @chvQuery = @chvQuery + 'i.CREATE_USR'+ ' '

  Select @chvQuery = @chvQuery + ' From #tmp_deleted d Inner Join #tmp_Inserted i '

  Select @chvQuery = @chvQuery + 'On d.SERV_ACCT_NO = i.SERV_ACCT_NO '

  Select @chvQuery = @chvQuery + 'WHERE d.' + @ColumnName + ' < > i.' + @ColumnName

  End

  Print @chvQuery

  Exec(@chvQuery)

  End

  Select @intColumn = @intColumn + 1

  Print 'You Are in Loop # ' + Convert(Varchar(3), @intColumn)

  END

  You can see I am using a bitwise routine to capture which columns have been updated.

  Thanks

  Mike

• October 30, 2002 at 8:00 am

  This was removed by the editor as SPAM