Forum Stats

  • 3,853,604 Users
  • 2,264,245 Discussions
  • 7,905,406 Comments

Discussions

What Oracle Database Feature to use

802562
802562 Member Posts: 101
edited Oct 26, 2010 6:15PM in General Database Discussions
What Oracle Database Feature to use to make automatic update of table based on child tables insert or update

For example


Table Invoice
1. Invoice No
2. Invoice Date
3. Desc
4. Total Price <-------------------------------|
|
|
Table Item_Sold |
1. Item sold ID |
2. Item selling price <---------------------------| sum of all records whith invoice no = to the parent table.
3. Inventory ID (FK to Inventory table)
4. Invoice no (FK to Invoice table)


The Invoice has one to many relationship with Item_sold, and the Total Price should be sum of Item selling price.

Any trigger a like or other cool Oracle features that can address this design?



Thank you.

Regards,
Sinardy
Tagged:

Answers

  • CKPT
    CKPT Cloud Advisory Manager Member Posts: 9,955 Silver Crown
    Tom Kyte has given a beautiful description on this,

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:130261000346380506

    Hope you can get answers , Thanks
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    Hi,

    I don' find any cool feature except some business logic flow needs to implement with respect to application requirement.
    Triggers in this case would not be efficient. Try to check from the Application flow, once the invoice got completed then, when application user submits or save the things from frontend make a call to procedure to update the relavant Total price in Parent table.

    - Pavan Kumar N
  • 802562
    802562 Member Posts: 101
    Why Trigger not efficient?
  • 802562
    802562 Member Posts: 101
    Hi CKPT,

    I have think of that solution, using store procedure to update the parent table, however my fear is say you have thousands of invoices and growing the codes will taking longer and longer to execute whenever there is update or insert or delete in child table.

    Regards,
    Sinardy
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    Hi,

    Trigger is relative to your event. Perhaps will you go for statement level update or row level update. Since today you carried out you invoice with 3 products - triggers execute based on your event type. Then with respect to customer requirement you might need to update or reduced (modify the invoice) - Trigger fires.
    Now, check the feasibility of Number of invoices per day generated , updations - Trigger event firing/exection sequences. Now, compare the manual process or custom logic with respect to ratio of trigger. You can get to know the stats and better usage of features.

    HTH

    - Pavan Kumar N
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,877 Red Diamond
    user11976229 wrote:
    Why Trigger not efficient?
    In this case the trigger code needs to hit a number of rows to do the rolling total calculation. For this calculation to be valid, it needs to prevent any other session from changing the data set in order for it to perform the calculation.

    For example, when calculating the current invoice sales for today, the current session needs to prevent other sessions from adding, deleting or inserting new invoices. Only after that has been done (and committed), the next transaction in-line can make its change (e.g. update an invoice) and it too now needs to prevent any other invoice changes while it calculates and updates the daily invoice amount total.

    This means that only a single transaction (change) can be allowed at a time on invoices in order to update the rolling totals. This means serialisation. Serialisation kills performance.

    Thus this approach (whether via a trigger or procedure) needs careful thought - as you can do serious damage to the ability of the database and data to be used by multiple processes and users.
  • jgarry
    jgarry Member Posts: 13,844 Gold Crown
    Billy is absolutely correct, of course. The system I work on has triggers, procedures and application code to do these kinds of operations in various places. It also tends to do things like set status flags to show someone is updating these things, and even has some operations that try to do something, then rollback and set a flag instead. There's also an app-specific emulation of calculated columns.

    This means all possible headaches - sessions die so I have to manually reset flags, app code has bugs so totals need to be fixed, rollbacks don't always seem to work as expected since transactions may start earlier than expected, people forget about the triggers and wonder why values are strange, people are locked out at odd times and so on.

    But having been pounded on by many customers over many years, it all seems to work, I don't have to fix that much, it's not overwhelming once you understand the app. In the end, all features will be used and abused whether appropriate or not. The best you can do is to try to encapsulate things as much as possible to minimize different styles leading to different results, and do as much as possible in the database rather than the app. Remember that a total in a parent is denormalized - attributes depend on the children, so in a design sense, it is cheating, and so has to be strange in a relational db.
This discussion has been closed.