create or replace trigger tri_user_material_sub
after insert on user_material_sub
for each row
/*declare
pragma autonomous_transaction;*/
begin
sp_saveMateriatoCache(:NEW.shtinsid, 3);
end;
由于触发器前有事务及存储过程里也有事务,因此在存储过程里面需要加上单独事务一遍两边分别运行:
create or replace procedure sp_saveMateriatoCache(p_Shtinsid Integer,
p_type Integer) as
pragma autonomous_transaction;----就是这个
x_Invorgid Integer;
x_Exists Integer;
x_ExistsCode Varchar2(2000);
begin
x_ExistsCode := null;
------新增
if p_type = 1 then
同时记得在存储过程里面加上commit; 不然又会多一个报错:
ORA-06519: 检测到活动的自治事务处理,已经回退
update cpcshtins s set s.wfflag = 2 where s.shtinsid = p_Shtinsid;
commit;-----关键
end sp_saveMateriatoCache;