Columns_Updated() in a Trigger

• michael_pereira

  SSC Veteran

  Points: 216

  More actions

  July 21, 2008 at 10:36 am

  #75266

  For i.e I introduce a new record in the 'clientes' table, and I would like to know witch related columns and tables are affected by this new introduced record.

  I'm working with this sample code, but its missing something...

  CREATE TRIGGER trg_Clientes ON Clientes

  FOR UPDATE

  AS

  BEGIN

  DECLARE @FldsUpdated XML

  DECLARE @ColumnsUpdated VARBINARY(100)

  SET @ColumnsUpdated = COLUMNS_UPDATED()

  SET @FldsUpdated =

  (SELECT TABLE_NAME, COLUMN_NAME

  FROM PRIDEMO.INFORMATION_SCHEMA.COLUMNS Field WHERE COLUMN_NAME = 'cliente' /*Primary key in 'clientes' table*/

  AND

  sys.fn_IsBitSetInBitmask

  (

  @ColumnsUpdated,

  COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')

  ) <> 0 ORDER BY TABLE_NAME

  FOR XML AUTO, ROOT('Fields'))

  /*Auxiliar table that stores the updates

  INSERT INTO ClientesHistoryAudit(cliente, nome, fac_mor, fac_local, fac_cploc, fac_tel, fac_fax, desconto,

  tipoprec, tipocred, limitecred, totaldeb, numcontrib, ColumnsUpdated)

  SELECT cliente, nome, fac_mor, fac_local, fac_cploc, fac_tel, fac_fax, desconto, tipoprec, tipocred, limitecred,

  totaldeb, numcontrib, @FldsUpdated

  FROM INSERTED

  END

  GO

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  July 21, 2008 at 5:55 pm

  #846084

  New record create whole new set, so it always affects all columns.

  Even if you don't insert anything meaningfull in there.

  _____________
  Code for TallyGenerator