0

I have an AFTER UPDATE trigger on a db table, Foo. It INSERTS an audit record into an Audit table whenever an UPDATE occurs on Foo.

There are 3 separate services running and sometimes they UPDATE the same Foo record -but different fields.

PROBLEM is when there are 3 UPDATES on the SAME row -which seems to be happening almost at the same time- I can see that ONLY one AFTER UPDATE trigger is fired. I see only ONE Audit record wheres I expect 3.

Any advise why this can happen?

pencilCake
  • 51,323
  • 85
  • 226
  • 363

1 Answers1

0

I can think of these reasons:

  1. An AFTER UPDATE trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires. [Quote] It may be that the other updates fail without you noticing.
  2. If the UPDATE is part of a long-running transaction, the other updates might be waiting for the first one to commit.
  3. You have a bug in your trigger.

Anyway, have you had a look into the auditing features that SQL Server offers out of the box?

Twinkles
  • 1,984
  • 1
  • 17
  • 31