Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
AFTER UPDATE
AS
  IF ( UPDATE(order_status) )
  BEGIN
    update jobs
      set  transfercomplete = case
                                when o.order_status = 'initial' and n.order_status = 'sent' then current_timestamp
                                else n.transfercomplete
                              end,
           installcomplete = case
                               when o.order_status = 'delivered' n.order_status = 'installed' then current_timestamp
                               else n.installcomplete
                            end
      from inserted n
        join deleted o on o.jobid = n.jobid
        join jobs aj on aj.jobid = n.jobid;
  END

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.

share|improve this question
2  
SQL Server has INSTEAD OF triggers that can be applied to tables. Not sure how similar that is to before update triggers? –  Martin Smith Nov 5 '12 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? –  a_horse_with_no_name Nov 5 '12 at 13:34

1 Answer 1

up vote 3 down vote accepted

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...

share|improve this answer
    
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? –  a_horse_with_no_name Nov 5 '12 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. –  Remus Rusanu Nov 5 '12 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. –  a_horse_with_no_name Nov 5 '12 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... –  Remus Rusanu Nov 5 '12 at 14:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.