人事管理系统Oracle触发器代码

--创建基于考核表上的触发器，在录入数据时依据总评进行考核等级的输入create or replace trigger WR
before insert on WorkReview
for each row
begin
:new.Wresult:=:new.Wattendence*0.3+:new.Wwork*0.7;
if :new.Wresult>=0 and :new.Wresult<=59 then
:new.Wstatus:='不及格' ;
elsif :new.Wresult>= 60 and :new.Wresult<= 69 then
:new.Wstatus:='及格';
elsif :new.Wresult >= 70 and :new.Wresult<= 89 then
:new.Wstatus:='良好' ;
elsif :new.Wresult >= 90 and :new.Wresult<= 100 then
:new.Wstatus:='优秀' ;
end if;
end;
---创建加班费可以在录入数据时自动计算其加班费进行加班费的结算
create or replace trigger Overtime_trigger
before insert on Overtime
for each row
begin
if:new.OverType='一般' then
:new.OverCost:= :new.OverTime*10;
elsif :new.OverType='节假' then
:new.OverCost:= :new.OverTime*10;
end if;
end;
-创建触发器LateCost_trigger在录入数据时对员工迟到进行自动扣费计算create or replace trigger LateCost_trigger
before insert on AttendRecord
for each row
begin
:new.Noattend:=:new.LeaveTime+:new.EveTime+:new.Awaywork;
--迟到3次以及3次以上扣费120
if :tetime>=3 then
:teCost:=120;
--迟到2次扣费80
elsif :tetime=2 then
:teCost:=80;
--迟到1次扣费40
elsif :tetime=1 then
:teCost:=40 ;
--迟到0次扣费0
elsif :tetime=0 then
:teCost:=0 ;
end if;
end;
--录入员工培训考核成绩得总评和考核等级
create or replace trigger TR
before insert on Trainresult
for each row
begin
:new.Trresult:= :new.Trattendence*0.3+ :new.Trtest*0.7;
if :new.Trresult >= 0 and :new.Trresult<=59 then
:new.Tstatus:='不及格';
elsif :new.Trresult >= 60 and :new.Trresult<=69 then
:new.Tstatus:='及格';
elsif :new.Trresult >= 70 and :new.Trresult<=89 then
:new.Tstatus:='良好';
elsif :new.Trresult >= 90 and :new.Trresult<=100 then
:new.Tstatus:='优秀';
end if;
end;
--当在员工工作变动时，在修改员工的基本信息时，将变动信息自动插入到工作变动单中--当员工的工资信息变动时，将变动信息插入到工资变动日志中
--录入加班费
create or replace procedure Caculate_OverCoat
is
begin
update Salary
set OverCost =
(select OverCost from Overtime
where Salary.Eno=Eno and to_char(OverDate,'mm’')=to_char(GrantDate,'mm'));
update Salary set OverCost=0 where OverCost is null;
commit;
end ;
--录入福利总额
create or replace procedure Calculate_Welfare
is
begin
update Salary
set Welfaretotal=
(select WelfareTota from Welfare
where Salary.Eno=Eno and to_char(StatisticMonth,'mm')=to_char(GrantDate,'mm'));
update Salary set Welfaretotal=0 where Welfaretotal is null;
commit;
end Calculate_Welfare;
--录入迟到扣费
create or replace procedure Calculate_LateCost
is
begin
update Salary
set LateCost=
( select LateCost from AttendRecord
where Salary.Eno=Eno and to_char(StatisticMonth,'mm')=to_char(Grantdate,'mm'));
update Salary set LateCost =0 where LateCost is null;
commit;
end Calculate_LateCost;
--录入个人所得税
create or replace procedure Calculate_Tax
is
begin
--月收入低于1500
update Salary
set Tax=(BasicSalary+OverCost+Welfaretotal-LateCost)*0.03
where (BasicSalary+OverCost+Welfaretotal-LateCost)<1500;
--月收入在1500和4500之间
update Salary
set Tax=(BasicSalary+OverCost+Welfaretotal-LateCost-1500)*0.1+1500*0.03
where (BasicSalary+OverCost+Welfaretotal-LateCost) > 1500 and (BasicSalary+OverCost+Welfaretotal-LateCost)<=4500;
commit;
end;
--实发金额
create or replace procedure Calculate_RealSalary
is
begin
update Salary
set RealSalary=(BasicSalary+OverCost+Welfaretotal-LateCost)-Tax ;
commit;
end Calculate_RealSalary;
/。