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; /
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.