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

1.什么是触发器

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

2.触发器的应用场景

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

3.最简单的触发器

3.0 触发器基本语法

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

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' 执行过程中出错