oracle 按表触发器更新列

wooyq4lh 于 2023-04-05 发布在 Oracle

关注(0)|答案(1)|浏览(237)

我有两张table：Orders和Orders_Detail. Orders：id，金额，折扣（0-100）;all是number. Orders_detail：id，id_order，price，qty，str_sum（pricequantity（1/discount/100）），idx（订单行数）;所有的都是数字。我需要的是，如果orders.discount改变了orders_detail.str_sum应该重新计数。

CREATE OR REPLACE TRIGGER TR_CHANGE_DIS
 BEFORE 
 UPDATE OF DISCOUNT
 ON ORDERS
 REFERENCING OLD AS O NEW AS N
 FOR EACH ROW 
declare
    p_idx number;
    p_price number;
    p_qty number;
    cursor price_qty_idx is
    select price,qty,idx from orders_detail where id_order = :n.id;
begin
    open price_qty_idx;
    loop 
    fetch price_qty_idx into p_price,p_qty,p_idx;
        exit when price_qty_idx%notfound;
        update orders_detail set str_sum=p_price * p_qty * (1-:n.discount/100) where id_order = :n.id and idx = p_idx;
    end loop;
end;

接下来我需要，如果orders_detail.str_sum改变（也如果插入或删除新行，并改变价格，数量，str_sum），orders.amount应该重新计数.这样做smth：

CREATE OR REPLACE TRIGGER TR_BID
 BEFORE 
 INSERT OR DELETE OR UPDATE OF STR_SUM, QTY, PRICE
 ON ORDERS_DETAIL
 REFERENCING OLD AS O NEW AS N
 FOR EACH ROW 
declare
    p_amount number;
    p_discount number;
begin
    if inserting then
        select nvl(discount,0) into p_discount from orders where id = :n.id_order;
        :n.str_sum := :n.price * :n.qty * (1-p_discount/100);
        select nvl(sum(str_sum),0) into p_amount from orders_detail where id_order = :n.id_order;
        p_amount := p_amount + :n.str_sum;
        update orders set amount = p_amount where id = :n.id_order;
    elsif updating then
        select nvl(p_discount,0) into p_discount from orders where id = :n.id_order;
        :n.str_sum := :n.price * :n.qty * (1-p_discount/100);
        select nvl(amount,0) into p_amount from orders where id = :n.id_order;
        p_amount := p_amount - :o.str_sum + :n.str_sum;
        update orders set amount = p_amount - :o.str_sum + :n.str_sum where id = :n.id_order;
    elsif deleting then
        select amount-:o.str_sum into p_amount from orders where id = :o.id_order;
        update orders set amount = p_amount where id = :o.id_order;
    end if;
end;

有可能吗？有什么想法吗？

oracle

来源：https://stackoverflow.com/questions/75874462/update-columns-by-table-trigger

1条答案

按热度按时间

vs91vp4v1#

将AFTER UPDATE用于ORDERS触发器：

CREATE OR REPLACE TRIGGER TR_CHANGE_DIS
 AFTER UPDATE OF DISCOUNT ON ORDERS
 FOR EACH ROW 
BEGIN
  UPDATE orders_detail
  SET   str_sum = price * qty * (1-:NEW.discount/100)
  WHERE id_order = :NEW.id;
END;
/

以及ORDERS_DETAIL的复合触发器（您不需要在str_sum发生更改时触发）：

CREATE TRIGGER tr_bid
FOR INSERT OR DELETE OR UPDATE OF QTY, PRICE ON ORDERS_DETAIL
COMPOUND TRIGGER
  TYPE ids_type IS TABLE OF ORDERS.ID%TYPE;
  ids ids_type := ids_type();
BEFORE EACH ROW
  IS
    v_discount ORDERS.DISCOUNT%TYPE;
  BEGIN
    IF NOT DELETING THEN
      SELECT discount
      INTO   v_discount
      FROM   orders
      WHERE  id = :NEW.id_order;

      :NEW.str_sum := :NEW.price * :NEW.qty * (1-v_discount/100);
    END IF; 

    IF :OLD.id_order IS NOT NULL AND :OLD.id_order NOT MEMBER OF ids THEN
      ids.EXTEND;
      ids(ids.COUNT) := :OLD.id_order;
    END IF;

    IF :NEW.id_order IS NOT NULL AND :NEW.id_order NOT MEMBER OF ids THEN
      ids.EXTEND;
      ids(ids.COUNT) := :NEW.id_order;
    END IF;
  END BEFORE EACH ROW;  
AFTER STATEMENT
  IS
  BEGIN
    FORALL i IN 1 .. ids.COUNT
      UPDATE orders
      SET   amount = (1 - discount/100) * (SELECT SUM(qty*price)
                                           FROM   orders_detail
                                           WHERE  id_order = ids(i))
      WHERE id = ids(i);
  END AFTER STATEMENT;
END;
/

fiddle

赞(0）回复(0）举报 2023-04-05

我来回答

oracle 按表触发器更新列

1条答案

相关问题

热门标签

最新问答