oracle学习笔记(二十九)：触发器

---

1、触发器是一个与表相关联的、存储的 PLSQL 程序，每当一个特定的 数据操作语言（insert、delete、update）在指定的表上发出时，oracle 自动的执行触发器中定义的 语句序列。

 

2、触发器的类型：

语句级触发器：

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

行级触发器（for each row）：

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

 

3、触发器可用于：

数据确认（后）；

安全性检查（前）；

 

4、触发器语法：

CREATE  [or REPLACE] TRIGGER  触发器名

{BEFORE | AFTER} ---- 之前触发 | 之后触发

{ INSERT | DELETE | UPDATE -----语句级触发

UPDATE OF 列名 }----行级

ON  表名

[FOR EACH ROW] ---- 行级触发

PLSQL 块【declare…begin…end;/】

---

5、创建语句级触发器 insertEmpTrigger，当对表【emp】进行增加【insert】操作前【before】，显示 "hello world"：

SQL> create or replace trigger insertEmpTrigger
  2  before
  3  insert
  4  on emp
  5  begin
  6      dbms_output.put_line('hello world');
  7  end;
  8  /

触发器已创建

6、查看已经创建好的触发器：

SQL> col TRIGGER_NAME for a16;
SQL> col TRIGGER_TYPE for a16;
SQL> col TRIGGERING_EVENT for a8;
SQL> col TABLE_NAME for a5;
SQL> col DESCRIPTION for a12;

SQL> select TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_NAME,DESCRIPTION from user_triggers;

TRIGGER_NAME     TRIGGER_TYPE     TRIGGERI TABLE DESCRIPTION
---------------- ---------------- -------- ----- ------------
INSERTEMPTRIGGER BEFORE STATEMENT INSERT   EMP   insertEmpTri
                                                 gger
                                                 before
                                                 insert
                                                 on emp

7、删除触发器：

SQL> drop trigger insertEmpTrigger;

触发器已删除。

8、使用 insert 语句插入一条记录，引起 insertEmpTrigger 触发器工作：

SQL> insert into emp(empno, ename, sal) values(1111, 'Jack', 2000);
hello world

已创建 1 行。

9、使用 insert 语句插入 N 条记录，引起 insertEmpTrigger 触发器工作：

SQL> insert into emp select * from xxx_emp;
hello world

已创建14行。

•注意：从 8、9 可以看出来，不管插入多少条数据，触发器只会执行一次；这种叫做 语句级触发器；

10、创建语句级触发器 deleteEmpTrigger，当对表【emp】进行删除【delete】操作后【after】，显示 "world 触发器"：

SQL> create or replace trigger deleteEmpTrigger
  2  after
  3  delete
  4  on emp
  5  begin
  6      dbms_output.put_line('hello 触发器');
  7  end;
  8  /

触发器已创建

11、使用 delete 语句删除一条记录，引起 deleteEmpTrigger 触发器工作：

SQL> delete from emp where empno = 1111;
hello 触发器

已删除 1 行。

12、星期一到星期五，且 7-23 点能向数据库 emp 表插入数据，否则使用函数抛出异常：

语法：raise_application_error('-20000', '例外原因')

SQL> create or replace trigger securityTrigger
  2  before insert on emp
  3  declare
  4      pday varchar2(10);
  5      phour number(2);
  6  begin
  7      -- 获取星期
  8      select to_char(sysdate, 'day') into pday from dual;
  9
 10      -- 获取时间
 11      select to_char(sysdate, 'hh24') into phour from dual;
 12
 13      -- 业务
 14      if ((pday in ('星期六', '星期日')) or (phour not between 7 and 23)) then
 15          -- 抛异常
 16          raise_application_error('-20000', '非工作时间，不能向 emp 表中插入数据');
 17      end if;
 18  end;
 19  /

触发器已创建

测试异常情况：

SQL> insert into emp(empno, ename, sal) values(1111, 'Jack', 2000);
insert into emp(empno, ename, sal) values(1111, 'Jack', 2000)
            *
第 1 行出现错误:
ORA-20000: 非工作时间，不能向 emp 表中插入数据
ORA-06512: 在 "SCOTT.SECURITYTRIGGER", line 14
ORA-04088: 触发器 'SCOTT.SECURITYTRIGGER' 执行过程中出错

13、创建行级触发器 checkSalaryTrigger，涨后工资这一列，确保大于涨前工资，语法：for each row/:new.sal/:old.sal

SQL> create or replace trigger checkSalaryTrigger
  2  after
  3  update of sal --在更新 sal 之后触发
  4  on emp
  5  for each row
  6  begin
  7      -- 如果 涨后工资 > 涨前工资
  8      if :old.sal > :new.sal then
  9          -- 抛异常
 10          raise_application_error('-20000', '工资不能越涨越低');
 11          dbms_output.put_line('涨工资失败');
 12      else
 13          dbms_output.put_line('涨工资成功');
 14      end if;
 15  end;
 16  /

触发器已创建

测试异常情况：

SQL> update emp set sal = sal - 100 where empno = 7369;
update emp set sal = sal - 100 where empno = 7369
       *
第 1 行出现错误:
ORA-20000: 工资不能越涨越低
ORA-06512: 在 "SCOTT.CHECKSALARYTRIGGER", line 5
ORA-04088: 触发器 'SCOTT.CHECKSALARYTRIGGER' 执行过程中出错

测试正常情况：

SQL> update emp set sal = sal + 100 where empno = 7369;
涨工资成功

已更新 1 行。

测试更新 多条数据的情况：可以看出，行级触发器每更新一条记录就会触发一次；

SQL> update emp set sal = sal + 100 where deptno = 10;
涨工资成功
涨工资成功
涨工资成功

已更新3行。

测试更新 别的字段的情况：可以看出，更新别的字段，触发器不会触发；只有更新 sal 字段，才会触发；

SQL> update emp set comm = comm + 100 where deptno = 10;

已更新3行。