对po_invoice表实现一个触发器,表关系如:
A(aa,bb)-B(bb,cc)-C(cc,dd,""),
要实现将修改A表时,将C表对应记录变为(cc,dd,bb)将代码如下:
create or replace trigger ia_sl
before update on po_invoice
referencing old as old_value new as new_value
for each row
declare
cursor 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' 执行过程中出错
A(aa,bb)-B(bb,cc)-C(cc,dd,""),
要实现将修改A表时,将C表对应记录变为(cc,dd,bb)将代码如下:
create or replace trigger ia_sl
before update on po_invoice
referencing old as old_value new as new_value
for each row
declare
cursor 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;
不行啊,加入后提示:
ORA-06519: 检测到活动的自治事务处理, 已经回退
ORA-06512: 在 "QSJH56.IA_SL", line 19
ORA-04088: 触发器 'QSJH56.IA_SL' 执行过程中出错