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

<Kenneth Koenraadt> wrote in message
news:3fdc1bc5.1509670_at_news.inet.tele.dk...
> 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
>

Thanks for the reply. I'll try my best to explain some of the background and why we're pursuing this approach. There's a lot more to all of this, and in my original post, I simplified things a great deal to isolate and clarify the issue at hand.

First of all, the data model and the application are Oracle's, not ours. We have been able (with Oracle's help) to extend and customize the data model to accommodate business-specific needs. The application, however, is code that we cannot modify. We've built some web interfaces to handle additional, business-specific functionality, but nothing that replaces the order processing application.

Secondly, there is actually a "hold status" field on both the Orders and Details tables, and dozens of hold statuses that we use. Each status has its own business rules. Some hold statuses are used just at the detail level (lines can have pricing holds, if the item's price cannot be resolved) and others apply to the entire order (like bad credit holds for certain customers).

So, in the application, users have the ability to make changes to order details (item, price, quantity, comments, etc.). Changes to certain detail fields may implicitly put that detail on some type of hold. For this scenario, we're saying that one hold status in particular now needs to apply to the entire order, not just that detail line. Again, we can't change or even see the application code.

I agree that triggers can be problematic (I've put in many hours of support work as proof) and are not the best solution where others exist. But, in pursuing this, I'm trying to dissuade the project team from using a far worse approach -- namely, scheduling a third-party process that runs every five minutes to audit the detail table. Yuck!

Hope that helps clarify. Thanks again.

-jk Received on Sun Dec 14 2003 - 10:19:13 CST