oracle 从另一个表触发递增值

eni9jsuy 于 2023-04-20 发布在 Oracle

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

当开具新发票时，分销商表中的“sold”属性需要递增。
invoice_ty如下：

create or replace type invoice_ty as object(
Invoice_no VARCHAR(10),
distributor ref distributor_ty,
customer ref customer_ty,
price NUMBER(6, 2)
);
 
Create or replace type Distributor_ty as object (
VAT VARCHAR(15),
Seq_No NUMBER,
Sold NUMBER,
Address Distributor_address_ty)
NOT FINAL;

此触发器返回可变表错误：

create or replace trigger increment_sold
after insert on invoice
for each row
BEGIN
update distributor set sold = sold + 1  where distributor.vat = treat(:new.distributor as ref distributor_ty).vat;
END;

有什么建议吗

oracle

来源：https://stackoverflow.com/questions/75983767/trigger-that-increment-value-from-another-table

1条答案

按热度按时间

wbgh16ku1#

使用复合触发器并收集分发服务器，以便从invoice表更新到AFTER EACH ROW子句中的集合中，然后更新AFTER STATEMENT子句中的distributors表：

CREATE TRIGGER increment_sold
FOR INSERT ON invoice
COMPOUND TRIGGER
  v_vats SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
AFTER EACH ROW
  IS
    v_vat DISTRIBUTOR.VAT%TYPE;
  BEGIN
    SELECT DEREF(:NEW.distributor).vat
    INTO   v_vat
    FROM   DUAL;

    v_vats.EXTEND;
    v_vats(v_vats.COUNT) := v_vat;
  END AFTER EACH ROW;  
AFTER STATEMENT
  IS
  BEGIN
    MERGE INTO distributor dst
    USING (
      SELECT COLUMN_VALUE AS vat,
             COUNT(*) AS num_sold
      FROM   TABLE(v_vats)
      GROUP BY COLUMN_VALUE
    ) src
    ON (src.vat = dst.vat)
    WHEN MATCHED THEN
      UPDATE
      SET    sold = dst.sold + src.num_sold;
  END AFTER STATEMENT;
END;
/

fiddle

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

我来回答

oracle 从另一个表触发递增值

1条答案

相关问题

热门标签

最新问答