Post reply

Update Trigger ?

  • May 12, 2004 at 1:30 am

    #87149

    Hi,

    I have  a parent child relationship between two parents (Order Header and Order Line tebles). I need to update the Total Cost in the order header with the sum of all related costs in the order line table. Multiple users may be accessing the same orders and order lines.

    What is the best way to go about this? I had though about creating un update and delete trigger on the order line table which would fire when any changes are made to the order line table to update the order header table with the total. Is this the best way to go about this ? What should I code in the Update and delete triggers. (I am quite new to triggers).

    Thanks

    CCB

  • May 13, 2004 at 5:47 am

    #506279

    If the total cost must be updated immediately, this is best done with a trigger. If you can stand a little lag I might consider a job that updates costs in batch periodically.

    If you go the trigger route here are a few things to keep in mind when writing your trigger.

    1. Remember that triggers are effectively nested transactions. You want the trigger to do as little and be as fast as possible. Don't dump a bunch of extra logic in your trigger.
    2. Check to make sure the data relevant to the trigger is actually updated. In your case you should check to make sure the order line cost is updated. There's no sense in the trigger running an update calcuation if the cost has not changed.
    3. Make sure that your trigger will support updates and deletes to multiple row.

    Hope that helps. Like most things, when done right triggers are great, but when done wrong they can really zap performance.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply