oracle系列之触发器

什么是触发器

触发器是一个特殊的存储过程，是一个与表相关联的，存储的PL/SQL程序，每当一个特定的数据操作语句（insert，update，delete）在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

触发器的语法

如何创建触发器

--Print ’insert success‘ after insert record into the table.
create [or replace] trigger trigger_name 
{after|before} {insert|delete|update|of row_name}
on table_name
[for each row [when(条件)]]
declare
begin
    dbms_output.put_line('insert success!');
end;

触发器的类型

语句级触发器和行级触发器

语句级没有for each row，行级反之。
语句级触发器：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。
行级触发器：触发语句作用的每条记录都被触发。在其中使用:old和:new伪记录变量，识别值的状态。

insert into emp10 select * from emp where depno=10;
-- 3 rows of records
-- 语句级触发器：针对的是表，只执行一次。
-- 行级触发器：针对的是行，执行了3次。

触发器的具体应用场景

复杂的安全性检查

禁止在非工作时间插入新员工

--周末:to_char(sysdate, 'day') in ('星期六', '星期日')
--上班前，下班后:to_number(to_char(sysdate, 'hh24')) not between 9 and 18

create or replace trigger security_emp
before insert
on emp
-- declare 程序中不使用变量的话，这块可以不写
begin
    if to_char(sysdate, 'day') in ('星期六', '星期日') or 
        to_number(to_char(sysdate, 'hh24')) not between 9 and 18 then
        --禁止insert
        raise_application_error(-20001, '禁止在非工作时间插入新员工');
    end if;
end;

数据确认

涨工资不能越涨越少

--:old和:new 代表同一条记录，前者代表操作之前的值，后者反之。
create or replace trigger check_salary
before update
on emp
for each row
begin
    if :new.salary < :old.salary then
        raise_application_error(-20002, '涨薪水不带这么玩的,涨前'||:old.salary||'涨后'||:new.salary);    
    end if;
end;

实现审计功能

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

--创建表，用于保存审计信息
create table audit_emp
(
infomation varchar2(200);
);

create or replace trigger do_audit_emp_salary
after update
on emp
for each row
begin
    if :new.salary > 6000 then
        insert into audit_emp values('员工信息:'||:new.somerows);
    end if;
end;

完成数据的备份和同步

给员工涨薪水之后，自动备份新工资到备份表

create or replace trigger sync_salary
after update
on emp
for earch row
begin
    update emp_bak set sal=:new.sal where empno=:new.empno;
end;