I'm coming from an Oracle/PostgreSQL background and have a hard time coping with the limits of SQL Server's trigger implementation.

I want to update two different datetime columns if the value of a different column changes. This is to record two specific status changes of the row.

Now SQL Server does neither have row level triggers, nor before update triggers. So my understanding is that I need to join insert and deleted to find out if and which status change occurred and then run a "regular" update agains the table being updated.

My trigger currently looks like this:

create trigger ord_status_history_trigger
  on jobs
  IF ( UPDATE(order_status) )
    update jobs
      set  transfercomplete = case
                                when o.order_status = 'initial' and n.order_status = 'sent' then current_timestamp
                                else n.transfercomplete
           installcomplete = case
                               when o.order_status = 'delivered' n.order_status = 'installed' then current_timestamp
                               else n.installcomplete
      from inserted n
        join deleted o on o.jobid = n.jobid
        join jobs aj on aj.jobid = n.jobid;

My question is: does this trigger effectively result in two updates on the table?

The first one being the triggering update and the second one being the one executed by the trigger?

Or is SQL Server smart enough to merge that into a single update to the base table (essentially the way it happens with a e.g. a BEFORE UPDATE trigger in PostgreSQL)

Do I create a potential performance bottleneck here?

We could set those timestamps from within the application as well, but it would be nicer if we could delegate this to the database, so we can be sure this is never forgotten.

  • 2
    SQL Server has INSTEAD OF triggers that can be applied to tables. Not sure how similar that is to before update triggers? Nov 5, 2012 at 13:29
  • @MartinSmith: as far as I can tell, the instead of update trigger requires the same update ... join inserted construct as a "regular" trigger. Or am I missing something?
    – user1822
    Nov 5, 2012 at 13:34

1 Answer 1


Your trigger runs after the updates have occurred on the table. If you update the table again in the trigger a new update is performed. See also the discussion about RECURSIVE_TRIGGERS.

If you want to run code before the update you will have to create an INSTEAD OF trigger.

Whether having to do two updates instead of one will become the bottleneck is entirely subject to the efficiency of the update in your trigger, and of course whether this UPDATE is already on the critical path or not. You probably have a primary key on job id, the pages are hot in buffer pool and you cannot have lock conflicts. So basically is only subject to a) how fast can your log accept writes and b) whether the version store (which serves the deleted and inserted pseudo-tables) can keep up.

If you ask me: such business logic status housekeeping belongs in the application, not in a trigger...

  • OK, thanks. As far as I can tell, the INSTEAD OF UPDATE trigger would still require the join and an UPDATE the same way as in a regular trigger. Or am I missing something?
    – user1822
    Nov 5, 2012 at 13:36
  • 2
    The INSTEAD OD trigger is code that runs instead of your UPDATE. If you want any write to occur, you must do it (ie. run UPDATE statement in the trigger). If you want to access the changed data, you must access it (ie. lookup the inserted/deleted pseudo tables). Not sure what are you asking. Nov 5, 2012 at 13:43
  • OK, understood. So I would need to make sure the UPDATE statement includes all columns of the table to not lose anything. So each time my table changes I would need to update the trigger? Seems quite a hassle. I guess we'll probably just update those columns from the application then.
    – user1822
    Nov 5, 2012 at 13:51
  • 1
    Correct, for an INSTEAD OF trigger is critical to keep the code in sync with the definition. You can use code generation tools, but overall is one more reason not to use triggers... Nov 5, 2012 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.