我是一个数据库的新手,写了一个触发器,想在触发器中查询被Update的表,但是报错:表发生了变化,触发器/函数不能读,请问有什么解决办法么?不胜感激!假设table_a(col1,col2,col3)
create or replace trigger tri before
update of col1 on table_a
for each rowdeclare
id decimal;
cursor cur is select col2 from table_a where col3=:new.col3;begin
open cur;
loop
fetch cur into id;
exit when cur%notfound;
........
insert......
end loop;
close cur;
end;
create or replace trigger tri before
update of col1 on table_a
for each rowdeclare
id decimal;
cursor cur is select col2 from table_a where col3=:new.col3;begin
open cur;
loop
fetch cur into id;
exit when cur%notfound;
........
insert......
end loop;
close cur;
end;
而你的cursor是根据col3的变化取的值,有点不通。
所以,
update of col1 on table_a
改成
update on table_a还有一个基于本表的trigger的变异问题需要考虑:
对于before 类型的 for each row 级别的triggers,如果使用 insert into ... values 语句触发此trigger ,则在trigger 中访问本table没有问题;
也就是说,你的 insert......语句需要改成insert into ... values 为好。请测试,将测试结果贴出来
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
我是想在一个员工的OrgID或IfManager列发生改变的时候,查询与他有上下级管理与被管理关系的员工,并在另一个日程代办的权限表(ScheduleAgent)里删除原有默认权限的记录,并根据该员工新的OrgID,和IfManager来重新在权限表ScheduleAgent中插入新的默认权限的记录(如,经理可以有员工的日程代办权限等等),所以要在update on Staff的时候查询Staff表看有哪些员工原来与这个被update的员工是同部门,或者是他的上司
触发器语句如下:
CREATE OR REPLACE TRIGGER UPDATESTAFF before
UPDATE OF IFMANAGER, ORGID ON STAFF
FOR EACH ROWdeclare
id decimal := :new.StaffID;org_id decimal := :new.OrgID;
manager smallint := :new.IfManager;
staff_id decimal;
fatherOrg_id decimal;
fatherStaff_id decimal;--12
childOrg_id decimal;
childManager_id decimal;
cursor cur is select StaffID from Staff where OrgID=org_id and IfManager=0;
cursor cur2 is select StaffID from Staff where OrgID=org_id and IfManager=1;
cursor cur3 is select StaffID from Staff where OrgID=fatherOrg_id and IfManager=1;--当所插员工是经理时,调用此游标找出所插员工所在部门的所有下属部门的部门ID
cursor cur4 is select OrgID from Org where FatherOrgID=org_id;
--查询childOrg_id部门的所有经理
cursor cur5 is select StaffID from Staff where IfManager=1 and OrgID=childOrg_id;begin
delete from ScheduleAgent where (OwnerStaffID=id or AgentStaffID=id) and IfSystem=1;
if manager=1 then
open cur;
loop
fetch cur into staff_id;
exit when cur%notfound;
insert into ScheduleAgent(AgentStaffID,OwnerStaffID,IfSystem) values(:new.StaffID,staff_id,1);
end loop;
close cur; --把该经理所在部门的父部门的所有部门经理添加代办日程权限,如果没有父部门则什么都不做。
select FatherOrgID into fatherOrg_id from Org where OrgID=org_id;
if fatherOrg_id is null then
fatherOrg_id :=null;
else
open cur3;
loop
fetch cur3 into fatherStaff_id;
exit when cur3%notfound;
insert into ScheduleAgent(AgentStaffID,OwnerStaffID,IfSystem) values(fatherStaff_id,:new.StaffID,1);
end loop;
close cur3;
end if;
--找该经理所在部门的所有下属部门,把每个下属部门的所有部门经理设置代办日程权限
open cur4;
loop
fetch cur4 into childOrg_id;
exit when cur4%notfound;
open cur5;
loop
fetch cur5 into childManager_id;
exit when cur5%notfound;
insert into ScheduleAgent(AgentStaffID,OwnerStaffID,IfSystem) values(:new.StaffID,childManager_id,1);
end loop;
close cur5;
end loop;
close cur4; end if; if manager=0 then
open cur2;
loop
fetch cur2 into staff_id;
exit when cur2%notfound;
insert into ScheduleAgent(AgentStaffID,OwnerStaffID,IfSystem) values(staff_id,:new.StaffID,1);
end loop;
close cur2;
end if;
end updatestaff;
真是不好意思,总结能力太差了,怕说不清楚自己的意思就把代码全都粘上了,烦劳各位再帮我看看吧!感谢感谢!