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

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_userafter inserton tb_userdeclarebegin  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_checkbefore inserton tb_userdeclarebegin  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 7ORA-04088: 触发器 'LEARN.TIME_CHECK' 执行过程中出错

4.2 案例二：数据的确认     

/*  触发器案例2：数据的确认  涨后工资不能少于涨前工资  1. :old ,:new 代表同一条记录  2. :old 表示操作该行之前，这一行的值     :new 表示操作改行之后，这一行的值*/create or replace trigger check_salarybefore updateon tb_userfor each rowbegin  -- 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 3ORA-04088: 触发器 'LEARN.CHECK_SALARY' 执行过程中出错

4.3案例三：数据库的审计

/*触发器应用场景三：数据库的审计 -->基于值的审计功能给员工涨工资，当涨后薪资超过6000时，审计该员工信息*/create table tb_audit(  infomation varchar2(200));create or replace trigger audit_userafter updateon tb_userfor each rowbegin  -- 当涨后工资超过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_salaryafter updateon tb_userfor each rowbegin  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_repeatbefore inserton tb_userfor each rowdeclarecursor cur_user is select name from tb_user;pname tb_user.name%type;beginopen 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 12ORA-04088: 触发器 'LEARN.CHECK_REPEAT' 执行过程中出错