How to create a trigger for updating a table for a quantity decrease?

190 pts.
Tags:
Oracle development
Triggers
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!

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following