create or replace trigger Bill_trig
after insert or update on bill
for each row
declare
v_bid bill.bid%type;
v_food_id bill.food_id%type;
v_quantity bill.quantity%type;
v_food_price food.food_price%type;
PRAGMA AUTONOMOUS_TRANSACTION;
sbid NUMBER;
cursor bill_cur is select bill.bid,bill.food_id,quantity,food_price from bill,food
where food.food_id=bill.food_id;
begin
open bill_cur;
fetch bill_cur into v_bid,v_food_id,v_quantity,v_food_price;
loop
exit when bill_cur%notfound;
select count(bid) into sbid from pay where bid=v_bid;
if sbid=0 then
insert into pay values(pay_sequence.nextval,v_bid,v_quantity*v_food_price,'未付');
else
update pay set money=v_quantity*v_food_price;
end if;
fetch bill_cur into v_bid,v_food_id,v_quantity,v_food_price;
end loop;
close bill_cur;
end;
运行无错,插入数据时报错。触发器
after insert or update on bill
for each row
declare
v_bid bill.bid%type;
v_food_id bill.food_id%type;
v_quantity bill.quantity%type;
v_food_price food.food_price%type;
PRAGMA AUTONOMOUS_TRANSACTION;
sbid NUMBER;
cursor bill_cur is select bill.bid,bill.food_id,quantity,food_price from bill,food
where food.food_id=bill.food_id;
begin
open bill_cur;
fetch bill_cur into v_bid,v_food_id,v_quantity,v_food_price;
loop
exit when bill_cur%notfound;
select count(bid) into sbid from pay where bid=v_bid;
if sbid=0 then
insert into pay values(pay_sequence.nextval,v_bid,v_quantity*v_food_price,'未付');
else
update pay set money=v_quantity*v_food_price;
end if;
fetch bill_cur into v_bid,v_food_id,v_quantity,v_food_price;
end loop;
close bill_cur;
end;
运行无错,插入数据时报错。触发器
ORA-06519:
active autonomous transaction detected and rolled back
Cause: Before returning from an autonomous PL/SQL block, all autonomous transactions started within the block must be completed (either committed or rolled back). If not, the active autonomous transaction is implicitly rolled back and this error is raised.
Action: Ensure that before returning from an autonomous PL/SQL block, any active autonomous transactions are explicitly committed or rolled back. ----------------------------------------------------------------------- 06520 through 06529 reserved for Foreign function errors也就是在触发器最后需要提交或者回滚事务
after insert or update on bill
for each row
declare
v_bid bill.bid%type;
v_food_id bill.food_id%type;
v_quantity bill.quantity%type;
v_food_price food.food_price%type;
PRAGMA AUTONOMOUS_TRANSACTION;
sbid NUMBER;
cursor bill_cur is select bill.bid,bill.food_id,quantity,food_price from bill,food
where food.food_id=bill.food_id;
begin
open bill_cur;
fetch bill_cur into v_bid,v_food_id,v_quantity,v_food_price;
loop
exit when bill_cur%notfound;
select count(bid) into sbid from pay where bid=v_bid;
if sbid=0 then
insert into pay values(pay_sequence.nextval,v_bid,v_quantity*v_food_price,'未付');
else
update pay set money=v_quantity*v_food_price;
end if;
fetch bill_cur into v_bid,v_food_id,v_quantity,v_food_price;
end loop;
close bill_cur;
commit;
end;