如题目所示,
定义如下:-----更改职位的存储过程:
create or replace procedure CgPos(
pos in table_hr.position%type,
id in table_hr.worker_id%type ) is
cn number;
begin
select count(*) into cn from table_hr where worker_id=id;
if cn>0 then
if pos='hr' or pos='采购' or pos = '销售' or pos =NULL then
update table_hr set position=pos where worker_id=id; --就这一句重复调用触发器
else dbms_output.put_line('职位不对!无效执行!');
rollback;
end if;
else dbms_output.put_line('没有该ID员工!无效执行!');
rollback;
end if;
commit;
end CgPos;
/
-----更改职位的触发器 修改模块儿权利值://========================就这一块儿有问题
create or replace trigger after_CgPosition_then_CgPower
after update of position on table_hr
for each row
begin
if :old.position ='hr' then
update table_hr set powerhr=1, powerBuy=0, powerSell=0, powerScan=1 where worker_id=:new.worker_id;
elsif :old.position ='采购' then
update table_hr set powerhr=0, powerBuy=1, powerSell=0, powerScan=1 where worker_id=:new.worker_id;
elsif :old.position ='销售' then
update table_hr set powerhr=0, powerBuy=0, powerSell=1, powerScan=1 where worker_id=:new.worker_id;
elsif :old.position =NULL then
update table_hr set powerhr=0, powerBuy=0, powerSell=0, powerScan=0 where worker_id=:new.worker_id;
else dbms_output.put_line('职位不对!无效执行!');
end if;
end after_CgPosition_then_CgPower;
/--执行:我希望更改 position 项值的时候 触发自动更改 power*** 几项的值。不过总是提示:ORA-04091: 表 JDKING.TABLE_HR 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "JDKING.AFTER_CGPOSITION_THEN_CGPOWER", line 4
ORA-04088: 触发器 'JDKING.AFTER_CGPOSITION_THEN_CGPOWER' 执行过程中出错
ORA-06512: 在 "JDKING.CGPOS", line 10
ORA-06512: 在 line 1学生刚刚开始学习plsql~希望大牛们指点~SQL触发器 存储过程PL/SQL
定义如下:-----更改职位的存储过程:
create or replace procedure CgPos(
pos in table_hr.position%type,
id in table_hr.worker_id%type ) is
cn number;
begin
select count(*) into cn from table_hr where worker_id=id;
if cn>0 then
if pos='hr' or pos='采购' or pos = '销售' or pos =NULL then
update table_hr set position=pos where worker_id=id; --就这一句重复调用触发器
else dbms_output.put_line('职位不对!无效执行!');
rollback;
end if;
else dbms_output.put_line('没有该ID员工!无效执行!');
rollback;
end if;
commit;
end CgPos;
/
-----更改职位的触发器 修改模块儿权利值://========================就这一块儿有问题
create or replace trigger after_CgPosition_then_CgPower
after update of position on table_hr
for each row
begin
if :old.position ='hr' then
update table_hr set powerhr=1, powerBuy=0, powerSell=0, powerScan=1 where worker_id=:new.worker_id;
elsif :old.position ='采购' then
update table_hr set powerhr=0, powerBuy=1, powerSell=0, powerScan=1 where worker_id=:new.worker_id;
elsif :old.position ='销售' then
update table_hr set powerhr=0, powerBuy=0, powerSell=1, powerScan=1 where worker_id=:new.worker_id;
elsif :old.position =NULL then
update table_hr set powerhr=0, powerBuy=0, powerSell=0, powerScan=0 where worker_id=:new.worker_id;
else dbms_output.put_line('职位不对!无效执行!');
end if;
end after_CgPosition_then_CgPower;
/--执行:我希望更改 position 项值的时候 触发自动更改 power*** 几项的值。不过总是提示:ORA-04091: 表 JDKING.TABLE_HR 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "JDKING.AFTER_CGPOSITION_THEN_CGPOWER", line 4
ORA-04088: 触发器 'JDKING.AFTER_CGPOSITION_THEN_CGPOWER' 执行过程中出错
ORA-06512: 在 "JDKING.CGPOS", line 10
ORA-06512: 在 line 1学生刚刚开始学习plsql~希望大牛们指点~SQL触发器 存储过程PL/SQL
解决方案 »
- 闪回操作
- 请问如何在pl/sql developer中查看一个包的存储过程的定义
- 遍历结果集导致的超时问题。紧急求救!!!
- oracle中有没有类似excel中的and函数可以实现形如:if(a1 and b1,100,50)的表达形式?
- OCIServerAttach()时coredump,有人懂吗?
- ubuntu 8.10 下怎么样安装oracle 10g?
- 问点关于ORACLE9I的问题!
- 大家帮下忙,看看这样得记录怎么取!
- (救救我吧)ORA触发器问题,主从表数据处理问题,急,在线等等!!!!!!
- 如何将图片存入oracle数据库?
- 函数中关于RowType类型作为参数的调用的问题
- ORACLE
--执行exec CgPos('hr',1);
存储没问题 ,主要是 触发器老不能用。。
建立触发器的不是create trig on 表名 然后在SQL操作,再SQL语句,你好像顺序错了···
after update of position on table_hr
for each row
declare
pragma autonomous_transaction;
begin
if :old.position ='hr' then
update table_hr set powerhr=1, powerBuy=0, powerSell=0, powerScan=1 where worker_id=:new.worker_id;
elsif :old.position ='采购' then
update table_hr set powerhr=0, powerBuy=1, powerSell=0, powerScan=1 where worker_id=:new.worker_id;
elsif :old.position ='销售' then
update table_hr set powerhr=0, powerBuy=0, powerSell=1, powerScan=1 where worker_id=:new.worker_id;
elsif :old.position =NULL then
update table_hr set powerhr=0, powerBuy=0, powerSell=0, powerScan=0 where worker_id=:new.worker_id;
else dbms_output.put_line('职位不对!无效执行!');
end if;
end after_CgPosition_then_CgPower;
/