Update Audit

• Kayal-717623

  Ten Centuries

  Points: 1390

  More actions

  June 10, 2008 at 3:26 pm

  #93488

  Here is a trigger to store the any update to a table. I just need the columns which was updated in my previous and current value which are xml data types.

  So my trigger goes like this.

  Create TRIGGER [InsertAuditOnUpdate]

  ON [dbo].[Borrower]

  AFTER UPDATE

  AS

  BEGIN

  DECLARE @FldsUpdated XML

  DECLARE @ColumnsUpdated VARBINARY(100)

  SET @ColumnsUpdated = COLUMNS_UPDATED()

  SET @FldsUpdated = ( SELECT COLUMN_NAME AS Name FROM INFORMATION_SCHEMA.COLUMNS Field WHERE TABLE_NAME = 'dbo.Borrower' AND sys.fn_IsBitSetInBitmask(@ColumnsUpdated,COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')) <> 0 FOR XML AUTO, ROOT('Fields'))

  INSERT INTO [AdventureWorks].[dbo].[AuditTable]

  ([UserID]

  ,[AuditDate]

  ,[TableName]

  ,[Action]

  ,[PrimaryKey]

  ,[PreviousValue])

  ,[CurrentValue])

  select system_user,getdate(),'dbo.borrower','Update',

  del.CustomerID,

  (select @FldsUpdated FROM INSERTED),(select @FldsUpdated FROM DELETED)

  FROM DELETED del

  END

  But the previous value and current value is not being populated for some reason.

  Please advise

  Thanks.

• bledu

  SSChampion

  Points: 12335

  More actions

  June 13, 2008 at 4:52 am

  #828359

  create an inner join in your select on the table and deleted tables

  ---

  Everything you can imagine is real.