Re: Help with Mutating Table - Are there other solutions?

Hi Contra,

At first, your design is dubious. Since you have a business rule which says "HOLD applies to either zero or all of my order lines, that is the whole ORDER" you shold have an attribute on ORDERS to keep track of that.

Secondly, why do you insist on triggers ? Why not build the check on line_status into the application which actually does the update ? Triggers are more awkward to maintain, don't scale well and should be avoided in the application.

• Kenneth Koenraadt

On Sat, 13 Dec 2003 11:39:23 -0500, "contrapositive" <contrapositive_at_hotmail.com> wrote:

>Two tables, ORDERS and DETAIL_LINES:
>
>ORDERS.order_id
>ORDERS.cust_id
>ORDERS.order_status, etc...
>
>DETAIL_LINES.order_id
>DETAIL_LINES.line_id
>DETAIL_LINES.line_status, etc...
>
>An order has one or more detail lines.
>
>We want an update trigger (for each row) on DETAIL_LINES that says if this
>line_status is being changed to HOLD, then all the other lines for this
>order should change to HOLD. Obviously, that means we're attempting to
>update the table that's being triggered, and the "mutating table" error is
>raised.
>
>I've seen the three triggers and a package approach to this, and I'm ready
>to implement it, but I can't help but think there's some (simpler) solution
>that I'm overlooking.
>
>Any ideas?
>
>Thanks.
>
>-jk
>
>
Received on Sun Dec 14 2003 - 02:22:10 CST