a triggers question in oracle..

Question

Hi, I am kind of new to triggers and cant figure it out how to resolve this.

After insert a new row on a specicfic table it should influence other tables aswell.

So if I add(insert) an order on a table which includes 3 quantity, I want to be 3 less In_stock in another table(column)... thanks in advance

Answer 1

Assuming some column and table names (order table column name : quantity and product_id as the key uniquely used to identify an order) .. this should do the job

create or replace trigger trg_update_available
   after insert on orders 
   for each row
begin
   update in_stock
      set quantity = quantity - :new.quantity
      where product_id = :new.product_id;
end;
/

Note : the commit; will still be present in the code where you insert the order.