which trigger event

• spin

  Hall of Fame

  Points: 3274

  More actions

  October 6, 2011 at 3:26 pm

  #390563

  hi

  i'm creating an audit table using a trigger. is there a way i can retrieve which trigger event fired (INSERT, UPDATE, DELETE)

  thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 6, 2011 at 3:36 pm

  #1391516

  Within the trigger...

  DECLARE @Operation VARCHAR(10)

  IF EXISTS (SELECT 1 FROM inserted) and NOT EXISTS (SELECT 1 FROM deleted)

  SET @Operation = 'Insert'

  IF EXISTS (SELECT 1 FROM inserted) and EXISTS (SELECT 1 FROM deleted)

  SET @Operation = 'Update'

  IF NOT EXISTS (SELECT 1 FROM inserted) and EXISTS (SELECT 1 FROM deleted)

  SET @Operation = 'Delete'

  That's one way.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• spin

  Hall of Fame

  Points: 3274

  More actions

  October 6, 2011 at 3:54 pm

  #1391520

  thanks that worked great.

  following on from that (maybe a new post?) can i return the column name that was updated?

  thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 6, 2011 at 3:59 pm

  #1391523

  Read up on the UPDATED() and COLUMNS_UPDATED () functions.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  October 7, 2011 at 8:43 am

  #1391789

  GilaMonster (10/6/2011)

  ---

  Read up on the UPDATED() and COLUMNS_UPDATED () functions.

  When doing UPDATEs, you should be aware that UPDATED() does not necessarily mean CHANGED. It simply means that a value was assigned to that column, not that the value is different from the original value. If you need to know whether the value changed, you need to compare the values in the INSERTED and DELETED tables.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• spin

  Hall of Fame

  Points: 3274

  More actions

  October 8, 2011 at 4:55 am

  #1392041

  thanks both. i now understand update() and columns_update()

  I found/modified this script which does the job...almost.

  --

  --DROP TRIGGER tr_SalesHistory

  CREATE TRIGGER tr_SalesHistory ON SalesHistory

  FOR UPDATE, INSERT

  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 = 'SalesHistory' AND

  sys.fn_IsBitSetInBitmask

  (

  @ColumnsUpdated,

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

  ) <> 0

  FOR XML AUTO, ROOT('Fields')

  )

  -- Which operation fired the triger : INSERT, UPDATE, DELETE

  DECLARE @Operation VARCHAR(1)

  IF EXISTS (SELECT 1 FROM inserted) and NOT EXISTS (SELECT 1 FROM deleted)

  SET @Operation = 'I'

  IF EXISTS (SELECT 1 FROM inserted) and EXISTS (SELECT 1 FROM deleted)

  SET @Operation = 'U'

  IF NOT EXISTS (SELECT 1 FROM inserted) and EXISTS (SELECT 1 FROM deleted)

  SET @Operation = 'D'

  INSERT INTO SalesHistoryAudit

  (SaleID, Product, SaleDate, SalePrice, ColumnsUpdated, Operation)

  SELECT SaleID, Product, SaleDate, SalePrice, @FldsUpdated, @Operation

  FROM INSERTED

  END

  GO

  It gets some fields from inserted and also creates some xml to capture which fields were updated.

  my next question: is there a way to take it further and get the value of the field into the xml?