How to prevent a derived value (SUM) from being manually updated to an incorrect value

Question

I am learning SQL and DB design for a college class. One assignment that was given to us is to create a table with a derived attribute which is the SUM of some child attributes. For example:

ORDERS
orderID {PK}
/orderTotal    /* derived from SUM of child itemTotals */

ITEMS
itemNo {PK}
orderID {FK}
itemTotal

Now, I am not even sure this is good practice. From some reading I've done on the web, derived values should not be stored, but rather calculated by user applications. I can understand that perspective, but in this instance my assignment is to store derived values and more importantly to maintain their integrity via triggers, which are relatively new to me so I am enjoying using them. I'd also imagine in some more complex cases that it really would be worth the saved processing time to store derived values. Here are the safeguards I've put in place which are NOT giving me problems:

A trigger which updates parent /orderTotal when new child item is inserted.

A trigger which updates parent /orderTotal when child item is deleted.

A trigger which updates parent /orderTotal when child itemTotal is modified.

However, there is another safeguard I want which I cannot figure out how to accomplish. Since the parent attribute /orderTotal is derived, it should never be manually modified. If somebody does attempt to manually modify it (to an erroneous value which is not actually the correct SUM), I want to either (a) prevent them from doing this or (b) revert it to its old value as soon as they are done.

Which is the better approach, and which is possible (and how)? I am not sure how to accomplish the former, and I tried to accomplish the latter via either a trigger or a constraint, but neither one seemed appropriate. The trigger method kept giving me ORA-04091 error for attempting to mutate the table which fired the trigger. The constraint method, I do not think is appropriate either since I'm not sure how to do such a specific thing inside a constraint check.

I am using Oracle SQL by the way, in SQL Developer.

Thanks!

Answer 1

"Now, I am not even sure thise is good practice."

Your intuition is right: this is bad practice. For some reason, a lot of college professors set their students the task of writing poor code; this wouldn't be so bad if they at least explained that it is bad practice and should never be used in the real world. But then I guess most professors have only a limited grasp on what matters in the real world. sigh.

Anyhoo, to answer your question. There are two approaches to this. One would be to use a trigger to "correct" i.e. swallow the change. This would be wrong because the user trying to modify the value would probably waste a lot of time trying to discover why their change wasn't sticking, without realising they were breaking a business rule. So, it's much better to hurl an exception.

This example uses Oracle syntax, because I'm guessing that's what you're using.

create or replace trigger order_header_trg
    before insert or update
    on order_header for each row
begin
    if :new.order_total != :old.order_total
    then
        raise_application_error 
                 ( -20000, 'You are not allowed to modify the value of ORDER_TOTAL');
    end if;
end;

The only problem with this approach is that it will prevent you inserting rows into ORDER_LINES and then deriving a new total for ORDER_HEADER.

This is one reason why denormalised totals are Bad Practice.

The error you're getting - ORA-04091 - says "mutating table". This happens when we attempt to write a trigger which selects from the table which owns the trigger. It almost always points to a poor data model, one which is insufficiently normalised. This is obviously the case here.

Given that you are stuck with the data model, the only workaround is a clunky implementation using multiple triggers and a package. The internet offers various slightly different solutions: here is one.

Answer 2

One solution might be to move all the logic for maintaining the orderTotal into an INSTEAD OF UPDATE trigger on the ORDERS table.

The triggers you already have in ITEMS can be simplified to update ORDERS without making a calculation - setting orderTotal to 0 or something like that. TheINSTEAD OF` trigger will run in place of the update statement.

If an attempt is made to manually update the order total, the INSTEAD OF trigger will fire and re-calculate the existing value.

PS - I don't have an Oracle DB to test on, but from what I can tell, an INSTEAD OF trigger will get around the ORA-04091 error - aplologies if this is wrong

EDIT Whilst this solution would work in some other RDBMS systems, Oracle only supports INSTEAD OF triggers on views.

EDIT 2 If the assignment allows it, a view could be a suitable solution to this problem, since this would enable the order value column to be calculated.