如何在pl sql中进行更新触发器查询

I have 2 tables (1) product_warehouse and (2) Return_Vendor Invoice. i have to update the quantity in product_warehouse by trigger according to the value of Return_Vendor Invoice Table. where Item_code is unique key in both tables.

我有2个表(1)product_warehouse和(2)Return_Vendor Invoice。我必须根据Return_Vendor发票表的值,通过触发器更新product_warehouse中的数量。其中Item_code是两个表中的唯一键。

For example if the product_warehouse contain 3 quantities , and the shopkeeper returns 1 quantity to vendor then it should be 2 in the Product_warehouse. update query will also acceptable.

例如,如果product_warehouse包含3个数量,并且店主向供应商返回1个数量,那么它应该在Product_warehouse中为2。更新查询也是可以接受的。

2 个解决方案

#1

create or replace
TRIGGER "WR_RETURN_INVOICE_UPDATE_TRG" 
AFTER UPDATE ON RETURN_INVOICE
FOR EACH ROW
  BEGIN
    UPDATE PRODUCT_WAREHOUSE
    SET QUANTITY=QUANTITY-:OLD.QUANTITY
    WHERE ITEM_CODE=:OLD.ITEM_CODE;   
END WR_RETURN_INVOICE_UPDATE_TRG;



Try this one it will works.

#2

create or replace trigger tiuda_return_vendor after insert or update or delete
on return_vendor is
begin
  update product_wherehouse
  set
    quantity = quantity - (:new.quantity - nvl(:old.quantity, 0)
  where
    item_code = nvl(:new.item_code, :old.item_code);
end;

This trigger works in most cases. You can insert update or delete the return line.

此触发器在大多数情况下有效。您可以插入更新或删除返回行。

Only thing is: when updating, you cannot update the item_code itself, because the update statement doesn't take that into account. You could easily solve that, but I don't know if it's in your requirements. I usually don't change values like that, but rather remove the item and add a new line for a different item.

唯一的事情是:更新时,您无法更新item_code本身,因为更新语句不考虑这一点。你可以很容易地解决这个问题,但我不知道它是否符合你的要求。我通常不会更改这样的值,而是删除该项并为其他项添加新行。

Updating the quantity works fine. If you update the quantity, the difference between old and new is calculated and that difference is used to modify the stock quantity in the wherehouse.

更新数量工作正常。如果更新数量,则计算旧旧之间的差异,并使用该差异修改wherehouse中的库存数量。

#1

create or replace
TRIGGER "WR_RETURN_INVOICE_UPDATE_TRG" 
AFTER UPDATE ON RETURN_INVOICE
FOR EACH ROW
  BEGIN
    UPDATE PRODUCT_WAREHOUSE
    SET QUANTITY=QUANTITY-:OLD.QUANTITY
    WHERE ITEM_CODE=:OLD.ITEM_CODE;   
END WR_RETURN_INVOICE_UPDATE_TRG;



Try this one it will works.

#2

create or replace trigger tiuda_return_vendor after insert or update or delete
on return_vendor is
begin
  update product_wherehouse
  set
    quantity = quantity - (:new.quantity - nvl(:old.quantity, 0)
  where
    item_code = nvl(:new.item_code, :old.item_code);
end;