How to create a trigger for updating a table for a quantity decrease?
How do you create a trigger to take care of the process of updating a table, to reflect a change downward in the quantity column?
1. The trigger needs to be an AFTER INSERT trigger on the above table so the trigger will not fire in case of exceptions.
2. The trigger must fire for each row that is inserted into the table.
3. The trigger will only involve the statement to lessen the quantity amount in the table for the example...movie ID.
Thanks!
Software/Hardware used:
Software/Hardware used:
ASKED:
July 16, 2008 3:48 PM
UPDATED:
July 16, 2008 8:11 PM
Answer Wiki:
You need to create two triggers one is row level where you will move your ID's into a global staging area (may be a table of record or temporary table), then in the statement level trigger you will handle all the updates on the ID's which you have saved in row level trigger.
The reason is, you will get “ORA-04091: table <table name> is mutating, trigger/function may not see it” error, if you try to run any DML statement in row level trigger on the triggered table or the referencing table. The main reason is for this error is the way Oracle manages the read consistent data.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
