Oracle触发器

触发器：特殊的存储过程，是一个与表相关联的、存储的PL/SQL程序

 

作用：每当一个特定的数据操作语句（insert、update、delete）在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

 

第一个触发器：每当成功插入新员工后，自动打印“成功插入新员工”

触发器的单词：trigger

1. 创建触发器

create trigger saynewemp

after insert

on emp

declare

begin

        dbms_output.put_line(‘成功插入新员工’);

end;

/

saynewemp:触发器名字

emp:与触发器相关联的表

 

 

触发器的语法：

CREATE [or REPLACE] TRIGGER 触发器名

{BEFORE|AFTER}

{DELETE|INSERT|UPDATE[OF列名]}

ON 表名

[FOR EACH ROW[WHEN(条件)]]

PLSQL块

 

[OF列名]：只有某列被更新时才触发

[FOR EACH ROW[WHEN(条件)]]：行级触发器才有该语句

 

 

触发器的类型：

语句级触发器(针对的是表)：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行

 

行级触发器（针对的是行）：触发语句作用的每一条记录都被触发。在行级触发器前使用:old和:new伪记录变量，识别值的状态。

 

 

触发器的应用场景：

  1、复杂的安全性检查

禁止在非工作时候叫你插入新员工

/*

1. 周末：to_char(sysdate,’day’)in(‘星期六’,’星期日’)
2. 上班前，下班后：to_number(to_char(sysdate,’hh24’)) not between 9 and 18

*/

create or replace trigger securitemp

before insert

on emp

begin

if to_char(sysdate,’day’)in(‘星期六’,’星期日’) or

to_number(to_char(sysdate,’hh24’)) not between 9 and 18

       --禁止insert 新员工

       raise_application_error(‘20001’,’禁止在非工作时间插入新员工’);

end if;

end;

/

 

 

2、数据确认

/*

涨后的薪水不能少于涨前的薪水

1、:old和:new  代表同一条记录

2、:old：表示操作该行之前，这一行的值

:new：表示操作该行之后，这一行的值

 

*/

create or replace trigger checksalary

before update

on emp

for each row

begin

--if 涨后的薪水<涨前的薪水then

       --if:new.sal<:old.sal then

       raise_application_error(-20002,’涨后的薪水不能少于涨前的薪水,涨后的薪水’||:new.sal||’涨前的薪水’||:old.sal);

       end if;

end;

/

 

 

   3、实现审计功能（基于值的）

/*

给员工涨工资，当涨后的薪水超过6000块时候，审计该员工的信息

*/

--创建表，用于保存审计信息

create table audit_info

(

Informatian varchar2(200)

);

 

create or replace trigger do_audit_emp_salary

after update

on emp

begin

       --当涨后的薪水大于6000，插入审计信息

       If:new.sal>6000 then

        Insert into audit_info values(:new.empno||’ ‘||:new.ename||’ ‘||:new.sal);

end if;

/

 

4、完成数据的备份和同步

/*

当给员工涨完工资后，自动备份新的工资到备份表中

*/

       create or replace trigger sync_salary

       after update

       on emp

       for each row

       begin

       --当主表更新后，自动更新备份表

       update emp_back set sal=:new.sal where empno=:new.empno;

       end;

       /