对po_invoice表实现一个触发器,表关系如:
A(aa,bb)-B(bb,cc)-C(cc,dd,""),
要实现将修改A表时,将C表对应记录变为(cc,dd,bb)将代码如下:
- SQL code
create or replace trigger ia_sl before update on po_invoice referencing old as old_value new as new_value for each row declarecursor test_cursor is select ntaxmny, ia_bill_b.cbill_bid from ia_bill_b, po_settlebill_b, po_invoice_b, ia_bill, po_invoice where po_invoice.cinvoiceid = po_invoice_b.cinvoiceid and ia_bill_b.csourcebillitemid = po_settlebill_b.csettlebill_bid and po_settlebill_b.cinvoice_bid = po_invoice_b.cinvoice_bid and ia_bill_b.cbillid = ia_bill.cbillid and ia_bill.bestimateflag = 'N' and po_invoice.cinvoiceid = :new_value.cinvoiceid;begin for nx in test_cursor loop update ia_bill_b set vdef1 = nx.ntaxmny where ia_bill_b.cbill_bid = nx.cbill_bid; end loop;END;
运行报错:
ORA-04091: 表 QSJH56.PO_INVOICE 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "QSJH56.IA_SL", line 2
ORA-06512: 在 "QSJH56.IA_SL", line 17
ORA-04088: 触发器 'QSJH56.IA_SL' 执行过程中出错
------解决方案--------------------
不能对基表进行操作。
用自治事务试下,在declare里面加入
PRAGMA AUTONOMOUS_TRANSACTION;
------解决方案--------------------