当另一个表的值发生更改时更新表的列值

axkjgtzd  于 2022-09-18  发布在  Java
关注(0)|答案(1)|浏览(200)

我有3张表(库存、订单主控、订单明细)。在表Inventory中,我有ID_PRODUCT和QUANTITY_INSTORTS。在表OrderMaster中,我有ID_ORDER和ID_STATE_OF_ORDER(所有行在开始时都是3)。最后但并非最不重要的是,在表OrderDetail中,我有ID_ORDER、ID_PRODUCT和QUANTITY_ORDERED。

我需要一个触发器,当OrderMaster中的ID_STATE_OF_ORDER更改为1时,表Inventory中的Quanity_Inventory将是当前金额减去QUANTY_ORDERED

我目前的触发器是这样的

  1. create or replace trigger Inventory_switch
  2. AFTER UPDATE on OrderMaster
  3. Begin
  4. UPDATE Inventory
  5. SET quantity_inventory =
  6. (SELECT Inventory.quantity_inventory - OrderDetail.quantity_ordered
  7. FROM Inventory, OrderDetail, OrderMaster
  8. WHERE Inventory.ID_PRODUCT = OrderDetail.ID_PRODUCT AND
  9. OrderMaster.ID_ORDER = OrderDetail.ID_ORDER AND
  10. OrderMaster.ID_STATE_OF_ORDER = 1);
  11. End;

此代码更新库存中的所有行,而不考虑我输入的条件。

拜托了,我会感激任何帮助的。先谢谢你。

ulmd4ohb

ulmd4ohb1#

既然是甲骨文,那么:

  • 您很可能遗漏了FOR EACH ROW子句(这样您就可以更新与其值已更新的id_order相关的行
  • 参考:new伪记录
  • when子句,以便在订单状态不为1时不会更新任何内容
  • exists子句,这样您就不会更新整个表,而只更新受影响的行

大概是这样的:

  1. create or replace trigger inventory_switch
  2. after update on ordermaster
  3. for each row
  4. when (new.id_state_of_order = 1)
  5. begin
  6. update inventory a set
  7. a.quantity_inventory =
  8. (select i.quantity_inventory - d.quantity_ordered
  9. from inventory i join orderdetail d on d.id_product = i.id_product
  10. where d.id_order = :new.id_order
  11. )
  12. where exists (select null
  13. from inventory i1 join orderdetail d1 on d1.id_product = i1.id_product
  14. where d1.id_order = :new.id_order
  15. );
  16. end;
  17. /
展开查看全部

相关问题