3

Short question since I don't know how to search for this. Can I "re-update" the same row? For example, I have a field that stores the sub-total of a payment, and given my business constraints I can update that value. Can I update the total of the same row with just a trigger? Thank you beforehand!

By the way I'm using Oracle and PL/SQL.

Business rules: I have the following. There's a table that stores will pays data, another table that stores the monthly fees to be paid, and another one that stores the possible discounts. One will pay can only be discounted once, and the will pay stores the subtotal and the total. So, what I'm doing is... "when the discount information is being updated, after it's committed, update the total value and the values of the fees".

Adam Hawkes
  • 7,218
  • 30
  • 57
Carlos Vergara
  • 3,592
  • 4
  • 31
  • 56

2 Answers2

4

You can't update the table your trigger is firing against, you'll get an ORA-04091 mutating table error. You can update values in the row itself, using the :NEW syntax, as long as it's a 'before' trigger.

I'm unclear what you mean about the subtotal though; it sounds like you have a running total field on the table; if thats based on other records on the same table (e.g. you have multiple records for the same order, and you want an inserted record to have the sum of all the previous ones). If that is the case then you can't do that either, as you'd hit the same ORA-04901.

If you're updating a row then you could adjust a field, by setting for example :NEW.subtotal := :OLD.subtotal - :OLD.value + :NEW.value, but not sure what the benefit of that field would be.

It would be helpful to see what your business logic is and how it fits in with the insert/update, and what you want the trigger to do. Often with something like this you really want to be using a wrapper procedure around the insert/update, rather than a trigger, but it's a bit vague at present.

For the subtotal to remain accurate, I'd probably avoid trying to maintain it all, and instead use a view which has an analytic function calculating it for you. Much less hassle, in my experience.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • @user1231958 - I'd definitely look at a stored procedure to do that. Probably doing the updates before the commit, to avoid the risk of simultaneous updates messing things up. – Alex Poole Feb 27 '12 at 15:31
  • What if for some reason someone got to the SQL*Plus console, or something similar, and inserted/updated the data in a wrong way, resulting in everything getting messed up? – Carlos Vergara Feb 27 '12 at 15:34
  • @user1231958 - this will sound glib, but if it's that fragile... don't let them. No-one should be connecting as the owner of the tables, they could be using other IDs and have access controlled by roles and grants. Then, don't give anyone insert/update privs on the table at all, *only* give them execute on the procedure (or more likely package of procedures). If that isn't possible for some reason, then a view calculating the numbers may still work. – Alex Poole Feb 27 '12 at 15:46
  • This is not possible, given chances are my professor will double check the table, so he will have this access. – Carlos Vergara Feb 27 '12 at 15:49
  • @user1231958 - I completely missed the homework tag. I'm not sure how it enforces the one-discount-only rule, but maybe you just need to set `subtotal` to `total - discount`, using a before-insert trigger. Really not sure what your prof is looking for here, or what you've been taught - the solution should bear some resemblance to what you're supposed to know already, I guess. – Alex Poole Feb 27 '12 at 16:01
  • Ah! I had to add that tag? Sorry. Anyways, so, I'm forced to use a before insert trigger, right? Apparently that's my best solution, thank you. – Carlos Vergara Feb 27 '12 at 16:02
1

Yes - a BEFORE INSERT for each row trigger can modify the values being inserted.

Adam Musch
  • 13,286
  • 2
  • 28
  • 32