question

pragya163 avatar image
pragya163 asked

Create a Trigger to update total count on one table when quantity is entered in another table

I have two tables: Product Table and purchase table. purchase table has supplier_id, Product_id, quantity, price etc and Product table has product_id, name, type etc. I want to create a trigger that will update Product_count in product table (product_count is a derived column) when product is purchased and entered in purchase table. How do I do that? I tried : Create or replace trigger trig_product_count after insert on purchase for each row begin update product set product_count=product_count+quantity where purchase_id=:new.purchase_id; exception ....... end trig_product_count; /
oracleupdatetrigger
7 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

For the future, kindly do tag your RDBMS as well. Thanks.
0 Likes 0 ·
Just in case if "**product\_count is a derived column**" means that the **product\_count** value is just a running total of all quantity values for a specific product, don't the lines reading update product set product_count=product_count+quantity where purchase_id=:new.purchase_id; have to reference the new.quantity and match by product_id, i.e. the lines should read update product set product_count=product_count+new.quantity where product_id=:new.product_id; I am not sure that the **purchase\_id** column should even exist in the product table. I mean people buy products and there is a reference to the **product_id** of the product they buy. The purchase record is uniquely identified by its respective **purchase_id**. There is a quantity associated with this product purchase. The **product_count**, which is essentially a running total of all purchases of a specific product gets updated whenever the new record is inserted into the purchase table. If what I suspect makes any sense then what do you want to do when the purchase is cancelled by the client? This might necessitate additional logic in your trigger if you use soft delete to cancel orders. Just my 2 cents Oleg
0 Likes 0 ·
I created an inventory table with (product_id, total_count) when a purchase is entered in purchase table(supplier_id, product_id,price, quantity) it should update the total_count in inventory table. the default value for total_count in inventory table is 0. for example when purchase is made (supplier, charger ,price,25) it should update the inventory ( Charger, 25). I am not being able to do that. here's my code: create or replace trigger trg_update_inventory after insert on supplier_product for each row begin update inventory set total_count=total_count+ quantity where ..... I am stuck at this point. how do I make the trigger get the quantity from the purchase table and update the total balance?
0 Likes 0 ·
pragya163 It looks like the script is my comment below your original question is correct and should do the trick if you replace 2 lines in your trigger with 2 lines in my comment. Please let me know if this works. To summarize, make your update looking like this: update inventory set total_count = total_count + new.quantity where product_id=:new.product_id;
0 Likes 0 ·
I tried that the error says : new. quantity not found.
0 Likes 0 ·
Show more comments

1 Answer

· Write an Answer
Usman Butt avatar image
Usman Butt answered
As you are talking about the tables, I will consider "derived column" as the virtual column. If it is the case then, AFAIK, you cannot do that. It is not the limitation of trigger but it is the overall limitation. The virtual column cannot be the target for INSERT and UPDATE. You should try to update one of the physical columns that constitutes the virtual column. If that is not possible or the virtual column is not dependent upon any physical column, then one workaround could be to have a separate column with 0 value by default. Add the new column in virtual column computation. And then update the new column in the trigger.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.