数据库编程入门(三)-触发器的应用

2017-03-10

1.什么是触发器

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

2.触发器的应用场景

复杂的安全性检查
数据确认
实现审计功能
完成数据的备份和同步

3.最简单的触发器

3.0 触发器基本语法


create or replace trigger 触发器名
before | after 
delete | insert | update [of 列名]
on 表名
[for each row [when 条件]]   -- 行级触发器 或 语句级触发器

3.1 语句级触发器

在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 – 针对的是表

3.2行级触发器

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


create or replace TRIGGER say_hi_to_new_user
after insert
on tb_user
declare
begin
  dbms_output.put_line('Hello New User...');
end;
/
insert into tb_user(no,name,salary,job,hiredate,deptno) values(10,'NikoBelic',5000,'MANAGER',sysdate,20);

输出

1行已插入。

Hello New User...

4.触发器应用实例

4.1 案例一:复杂安全性检查禁止在非工作时间插入员工


create or replace trigger time_check
before insert
on tb_user
declare
begin
  if to_char(sysdate,'day') in ('星期六','星期日') or
     to_number(to_char(sysdate,'hh24')) not between 9 and 17 then
     RAISE_APPLICATION_ERROR(-20001, '禁止在非工作时间插入新员工');
  end if;
end;
/
insert into tb_user(no,name,salary,job,hiredate,deptno) values(11,'NikoBelic',5000,'MANAGER',sysdate,20);

输出：
错误报告 - SQL 错误: ORA-20001: 禁止在非工作时间插入新员工 ORA-06512: 在 "LEARN.TIME_CHECK", line 7 ORA-04088: 触发器 'LEARN.TIME_CHECK' 执行过程中出错

4.2 案例二：数据的确认


/*
  触发器案例2：数据的确认
  涨后工资不能少于涨前工资
  1. :old ,:new 代表同一条记录
  2. :old 表示操作该行之前，这一行的值
     :new 表示操作改行之后，这一行的值
*/
create or replace trigger check_salary
before update
on tb_user
for each row
begin
  -- if 涨后的工资 < 涨前的工资 then
  if :new.salary < :old.salary then
    RAISE_APPLICATION_ERROR(-20002, '涨后工资比涨前工资还低?滚!');
  end if;
end;
/
update tb_user t set t.salary = t.salary - 100 where t.no = 1;

输出：
错误报告 - SQL 错误: ORA-20002: 涨后工资比涨前工资还低?滚! ORA-06512: 在 "LEARN.CHECK_SALARY", line 3 ORA-04088: 触发器 'LEARN.CHECK_SALARY' 执行过程中出错

4.3案例三：数据库的审计


/*
触发器应用场景三：数据库的审计 -->基于值的审计功能
给员工涨工资，当涨后薪资超过6000时，审计该员工信息
*/
create table tb_audit(
  infomation varchar2(200)
);
create or replace trigger audit_user
after update
on tb_user
for each row
begin
  -- 当涨后工资超过6000时，插入审计信息
  if :new.salary > 6000 then
    insert into tb_audit(infomation) values(:new.name || ' ' || :new.salary);
  end if;
end;
update tb_user t set t.salary = t.salary + 2000;

4.4 案例四：数据的备份和同步

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


create table tb_user_back as select * from tb_user;
create or replace trigger sync_salary
after update
on tb_user
for each row
begin
  update tb_user_back set salary = :new.salary where no = :new.no;
end;
/
update tb_user t set t.salary = t.salary + 1 where t.no = 1 ;

4.5 案例5, 防止数据重复插入

此案例结合存储过程锁，可以有效防止高并发问题。

create or replace trigger check_repeat
before insert
on tb_user
for each row
declare
cursor cur_user is select name from tb_user;
pname tb_user.name%type;
begin
open cur_user;
    loop
      fetch cur_user into pname;
      exit when cur_user%notfound;
      dbms_output.put_line(pname);
      if :new.name = pname then
        RAISE_APPLICATION_ERROR(-20003, '该用户已经被存储过了');
      end if;
    end loop;
end;
/
insert into tb_user(no,name,salary,job,hiredate,deptno) values(12,'Zhangyu',5000,'MANAGER',sysdate,20);

输出
在行: 23 上开始执行命令时出错 - insert into tb_user(no,name,salary,job,hiredate,deptno) values(12,'Zhangyu',5000,'MANAGER',sysdate,20)
错误报告 - SQL 错误: ORA-20003: 该用户已经被存储过了 ORA-06512: 在 "LEARN.CHECK_REPEAT", line 12 ORA-04088: 触发器 'LEARN.CHECK_REPEAT' 执行过程中出错