Take the 2-minute tour ×

How can I create a trigger that will check one field of created View. Basically, a field in my View table displays total amount of products added to database. The trigger will have a specified maximum total amount so when the sum of products added to database will reach the maximum the trigger should alert user and make it impossible to add more products.

By the way, I'm not sure if setting triggers with views is the best way to achieve my aim. I'm using Oracle 11g

share|improve this question
add comment

2 Answers

I'm not sure how your view looks, but you can set an AFTER INSERT OR UPDATE TRIGGER(so, not a for each row trigger) which can ckeck like

v_amt number;
  select sum(amount) into v_amt from products_table;
  if v_amt > certain_amount then raise_application_error(-200001,'You cannot add more than '||certain_amount||' amount into the database');
  end if;

Also you can do this on application layer, after insert and before commit. But if you have many sources of data, the constraint on database layer is better.

share|improve this answer
Just be aware that this will fail with concurrent sessions; neither session will see the newly inserted data from the other, and the trigger will not detect the error. –  Jeffrey Kemp May 7 '13 at 8:28
@JeffreyKemp Yes, I didn't take this into account. You are right and David Aldridge's answer points this and also points a solution(DBMS_LOCK). –  Florin Ghita May 7 '13 at 9:23
add comment

It sounds like you want the trigger logic to be based on multiple rows of the table, and to deal with simultaneous modifications to the table by various sessions.

This will not work, as each session cannot see the changes being made by others until after they are committed, so two sessions can simultaneously add rows to the system, see that they are not themselves exceeding the limit on products, and commit. The limit can be exceeded by the combination of changes.

The only safe ways of doing this are to serialise modifications to the tables (DBMS_Lock being the best mechanism for that), or to use a fast on commit refreshing materialised view to sum the amount to be checked to a single row with a constraint placed on that column (this is effectively a serialising mechanism as well).

share|improve this answer
add comment

Your Answer


