日期:2014-05-17 浏览次数:20950 次
-----更改职位的存储过程:
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;
/