Oracle触发器正在给错误活动自治事务检测和回滚

Question

我写了一个触发器，在最后调用一个过程，过程有一个提交语句。当我执行触发器时，它会给我错误"不能在触发器中提交"。然后我声明PRAGAMA AUTONOMUS_TRANSACTION，但这也给出了错误"检测到活动自治事务并回滚";，触发器"执行时出现错误

CREATE OR REPLACE TRIGGER  Test_Ord_Update   
AFTER  
insert or update on test1  
for each row  

declare
PRAGMA AUTONOMOUS_TRANSACTION;
l_exst number(1);
v_id NUMBER (5);
begin

merge into test2 b
USING dual a
on (:new.id = b.id)
when matched then update set
b.price1 = :new.price1,
b.price2 = :new.price2
when not matched then insert (id, price1, price2)
values (:new.id, :new.price1, :new.price2);
select id into v_id from test2 where price1 =  :new.price1;
LOAD_PRICE(v_id);
end;

Answer 1

当我们在一个有提交语句的触发器中调用子程序时，我们必须在调用子程序的行后面写一个提交。像下面的

CREATE OR REPLACE TRIGGER Test_Ord_Update
AFTER
为每一行插入或更新test1

declare
PRAGMA AUTONOMOUS_TRANSACTION;
l_exst number(1);
v_id NUMBER (5);
begin
merge into test2 b
USING dual a
on (:new.id = b.id)
when matched then update set
b.price1 = :new.price1,
b.price2 = :new.price2
when not matched then insert (id, price1, price2)
values (:new.id, :new.price1, :new.price2);
select id into v_id from test2 where price1 =  :new.price1;
LOAD_PRICE(v_id);
COMMIT;
end;