工作中第一次用到触发器,特地总结留下记录,以做参考
create or replace trigger count2013
before insert --可以使用before或after确定触发时机,触发事件作可以是insert,delete,等其他dml语句和ddl语句等
on rec_employee_salary --确定触发的表
for each row --行级触发,省略则是语句级触发
when (new.term =201706 and new.pksi = 2013) --触发该触发器的条件
declare --可以利用declare声明所需变量
v_pkorg varchar2(20);
v_orgScore number;
begin
if inserting then --判断如果是插入数据操作则进入if语句
--获取该支行人员的所属机构,以及该机构的网点考核得分
select emp.pkorg into v_pkorg from (select * from rec_employee_salary where pksi = 201706 and pksi = 2013) empsal
inner join dic_employee emp
on empsal.pkemp = emp.pkemp;
select salarystandard into v_orgScore from dic_salary_standard where pksi = 5011 and pkemp = v_pkorg;
if v_pkorg in(14050702800,14050701300,14050700600,14050702400) then
:new.salary :=:new.salary+500*v_orgScore*0.01;
elsif v_pkorg in(14050701000,14050700700,14050701400,14050701900,14050701600,14050702700) then
:new.salary :=:new.salary+700*v_orgScore*0.01;
end if;
end if;
end;