当删除表中的数据的时候,报错了,是触发器/函数不能读它
我的触发器是当一个表中的数据改变(增、删、改)后,操作另一张表中的数据,对另一张表中的数据进行增删操作。
我修改后的触发器是这样的,在declare语句块里面加了“pragma autonomous_transaction;“,让oracle知道触发器是自定义事物处理。原因应该是当删除一张表中的数据时,我同时要修改另一张表的数据。如果删除时没有commit,则会影响另一张表的操作。因为这是两个事物。两个事物冲突了,就出现错误了。
下面是我修改后的触发器,修改了两点:一是在declare语句块加了“pragma autonomous_transaction;“ 二是在涉及到的insert、update、delete 语句后面加上了commit;
create or replace trigger AMC_SAP_VOUCHER_HR_QX_T
after insert or update or delete on xip.AMC_SAP_VOUCHER_HR_QX
for each row
declare
is_exist number;
is_exist_user_role number;
emp_count number;
pragma autonomous_transaction;
begin
case
when inserting then
select count(*) into is_exist from t_user_role r where r.c_roleid = 'I8ad99053015525f625f6e770015529d2fc3f0bf6'and r.c_userid = :new.empnumber;
if is_exist=0 then
insert into t_user_role values(:new.empnumber,'I8ad99053015525f625f6e770015529d2fc3f0bf6');
commit;
end if;
when updating then
if :new.empnumber=:old.empnumber then
return;
else
--删除旧的
select count(*) into emp_count from xip.AMC_SAP_VOUCHER_HR_QX t where t.empnumber = :old.empnumber;
if is_exist=0 then
select count(*) into is_exist_user_role from t_user_role r where r.c_roleid = 'I8ad99053015525f625f6e770015529d2fc3f0bf6'and r.c_userid = :old.empnumber;
if is_exist_user_role>0 then
delete from t_user_role tr where tr.c_userid = :old.empnumber and tr.c_roleid = 'I8ad99053015525f625f6e770015529d2fc3f0bf6';
commit;
end if;
end if;
--插入新增的
select count(*) into is_exist from t_user_role r where r.c_roleid = 'I8ad99053015525f625f6e770015529d2fc3f0bf6'and r.c_userid = :new.empnumber;
if is_exist=0 then
insert into t_user_role values(:new.empnumber,'I8ad99053015525f625f6e770015529d2fc3f0bf6');
commit;
end if;
end if;
when deleting then
--一个用户可能对应多个org,在数据库中有多条记录,删除某条用户并不代表把这个用户全部删除了
select count(*) into is_exist from xip.AMC_SAP_VOUCHER_HR_QX t where t.empnumber = :old.empnumber;
if is_exist=0 then
select count(*) into is_exist_user_role from t_user_role r where r.c_roleid = 'I8ad99053015525f625f6e770015529d2fc3f0bf6'and r.c_userid = :old.empnumber;
if is_exist_user_role>0 then
delete from t_user_role tr where tr.c_userid = :old.empnumber and tr.c_roleid = 'I8ad99053015525f625f6e770015529d2fc3f0bf6';
commit;
end if;
end if;
end case;
end AMC_SAP_VOUCHER_HR_QX_T;