I have 3 tables (Inventory, OrderMaster, OrderDetail). In the table Inventory I have ID_PRODUCT and quantity_inventory columns. In the table OrderMaster I have ID_ORDER and ID_STATE_OF_ORDER (all rows are 3 at the start). Last, but not least, inthe table OrderDetail I have ID_ORDER, ID_PRODUCT and quantity_ordered.

I need a trigger that when the ID_STATE_OF_ORDER in OrderMaster changes to 1, then quanity_inventory in the table Inventory will be the current amount minus quantity_ordered

The current trigger I have is

create or replace trigger Inventory_switch   
  AFTER UPDATE on OrderMaster            
  Begin 
    UPDATE Inventory
        SET quantity_inventory = 
        (SELECT Inventory.quantity_inventory - OrderDetail.quantity_ordered
        FROM Inventory, OrderDetail, OrderMaster
        WHERE Inventory.ID_PRODUCT = OrderDetail.ID_PRODUCT AND
            OrderMaster.ID_ORDER = OrderDetail.ID_ORDER AND
            OrderMaster.ID_STATE_OF_ORDER = 1);
End;

This code updates all the rows in Inventory regardless of the conditions I've entered.

Please, I would apreciate any help. Thanks in advance.

CodePudding user response：

As it is Oracle, then:

• you're missing the FOR EACH ROW clause (so that you'd update rows which are related to id_order whose values have been updated)
• reference to :new pseudorecord
• when clause, so that you wouldn't update anything if state of order isn't 1
• exists clause, so that you wouldn't update the whole table but only affected rows

Something like this:

create or replace trigger inventory_switch
  after update of id_state_of_order on ordermaster
  for each row
  when (new.id_state_of_order = 1
        and
        nvl(old.id_state_of_order, 0) <> 1
       )
begin
  update inventory i
  set
    i.quantity_inventory = i.quantity_inventory -
      (select sum(od.quantity_ordered)
       from orderdetail od
       where od.id_product = i.id_product
       and od.id_order = :new.id_order
      )
    where exists
      (select null
       from orderdetail od
       where od.id_product = i.id_product
       and od.id_order = :new.id_order
      );
end inventory_switch;
/