oracle 11g 的触发器

1. 简介

触发器是当特定事件出现时自动执行的存储过程，特定事件可以是执行更新的DML语句和DDL语句。触发器不能被显式调用，其有如下功能：

自动生成数据
自定义复杂的安全权限
提供审计和日志记录
启用复杂的业务逻辑

2. 创建

2.1 语法

触发器的创建语法如下，后面会以示例来说明：

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]] 
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN (condition)]
pl/sql_block;

2.2 组成

触发器由三部分组成：

触发器语句（事件）
- 定义激活触发器的 DML 事件和 DDL 事件
触发器限制
- 执行触发器的条件，该条件必须为真才能激活触发器
触发器操作（主体）
- 包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行

如下语法，可以简单地表现触发器的三个组成部分：

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF [INSERT] [[OR] UPDATE [OF column_list]] [[OR] DELETE] -- 1. 触发器事件
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}] [FOR EACH ROW][WHEN (condition)] -- 2. 触发器限制，在触发事件之上再做限制
pl/sql_block; -- 3. 触发器主体

2.3 示例说明

现有如下 student 表：

SQL> select * from student;

                                    SNO SNAME                                         SAGE
--------------------------------------- ---------- ---------------------------------------
                                      1 Tom                                             21
                                      2 Kite                                            22
                                      3 Bob                                             23
                                      4 Mike                                            24

2.3.1 触发事件

现在我们对 student 表进行插入、更新或操作，其中触发事件设定为每次插入、删除或更新 student 的一条记录就会触发触发器执行。编写对应的触发器如下：

create or replace trigger trig1 
before insert or update or delete -- 插入、更新或删除表示触发器的触发事件，即在插入、更新或删除等事件之前（before）触发执行
on student
begin -- 触发器主体
    dbms_output.put_line('触发器 trig1 响应了》》》');
end;
/

执行如下语句，其中的插入、更新和删除都会触发触发器的执行，并答应触发器 trig1 响应了》》》：

SQL> insert into student values (5, '李四', 52);

触发器 trig1 响应了》》》

1 row inserted


SQL> update student set sage = 25 where sno = 5;

触发器 trig1 响应了》》》

1 row updated


SQL> delete from student where sno = 5;

触发器 trig1 响应了》》》

1 row deleted


SQL> select * from student;

                                    SNO SNAME                                         SAGE
--------------------------------------- ---------- ---------------------------------------
                                      1 Tom                                             21
                                      2 Kite                                            22
                                      3 Bob                                             23
                                      4 Mike                                            24

2.3.2 for each row

创建如下触发器：

create or replace trigger trig1 
before insert or update or delete
on student
for each row -- 新增该短语
begin
    dbms_output.put_line('触发器 trig1 响应了》》》');
end;
/

这里我们使用了for each row 短语，其作用是使表级触发器变为行级触发器。在没有增加该短语时，我们使用如下语句更新 student 的多条记录：

1	update student set sage = sage + 10;

触发器执行响应结果如下所示，由于是表级触发器，所以无论更新多少条记录，触发器只触发一次：

SQL> update student set sage = sage + 10;

触发器 trig1 响应了》》》

4 rows updated

再增加了 for each row 短语之后，触发器的响应结果如下所示，即每更新一条记录都会触发一次：

SQL> update student set sage = sage + 10;

触发器 trig1 响应了》》》
触发器 trig1 响应了》》》
触发器 trig1 响应了》》》
触发器 trig1 响应了》》》

4 rows updated

2.3.3 before & after

before 的工作原理如下，简单地讲即是在数据入库之前触发触发器的执行：

after 的工作原理如下，简单地讲即是在数据入库之后触发触发器的执行：

现在我们创建如下触发器，即在对 student 表的 sage 列进行插入操作时，如果此时的学生记录的年龄若为负值则触发触发器执行，限制该记录入库，很明显我们应该需要使用 before 关键字，示例如下：

create or replace trigger trig1
before insert on student for each row -- 插入一条记录之前触发执行, 因为 NEW 或 OLD 引用不允许在表级触发器中，所以这里只能用行级触发器
begin
    if :new.sage < 0 then -- :new 表示新插入的记录，后面会进行介绍
        raise_application_error(-20001, '年龄错误，不能插入表中！'); -- 抛出自定义异常
    end if;
end;
/

插入执行结果如下所示：

SQL> insert into student values(5, 'lisi', -6);
insert into student values(5, 'lisi', -6)

ORA-20001: 年龄错误，不能插入表中！
ORA-06512: 在 "SCOTT.TRIG1", line 3
ORA-04088: 触发器 'SCOTT.TRIG1' 执行过程中出错

2.3.4 :new & :old

:new 表示一条新插入的记录，而 :old 表示原有的数据库数据记录。如果在触发器的 PL/SQL 块中使用 :new 或 :old，那么就必须是行级触发器，就是要有 for each row。很容易理解，因为 :new 和 :old 表示一行记录，所以触发器是需要作用于行的行级触发器，而不是作用于表的表级触发器。

需要说明的是，当执行 insert 的时候，:new 存在而 :old 不存在；当执行 delete 的时候，:new 不存在而:old 存在；当执行 update 的时候，:new 存在且 :old 也存在。由于 oracle 底层在执行 update 语句时，实质是先删除记录再插入新的记录的，所以才有:new 存在且:old 也存在的情况。

3. 类型

触发器有如下类型：

DDL 触发器：在模式中执行 DDL 语句时执行，比如建表、修改表时触发的触发器；
数据库级触发器：在发生打开、关闭、登录和退出数据库等系统事件时执行；
DML 触发器：在对表或视图执行DML语句时执行，比如更新、插入时触发的触发器；
表级触发器：无论受影响的行数是多少，都只执行一次；
行级触发器：对DML语句修改的每个行执行一次；
INSTEAD OF 触发器：用于用户不能直接使用 DML 语句修改的视图；

4. INSTEAD OF 触发器

现在有 student 表和 score 如下所示：

SQL> select * from score;

STUDENT_NO SCORE
---------- -----
         1    56
         2    82
         3    90
         
SQL> select * from student;

                                    SNO SNAME                                         SAGE
--------------------------------------- ---------- ---------------------------------------
                                      5 lisi                                            -6
                                      1 Tom                                             31
                                      2 Kite                                            32
                                      3 Bob                                             33
                                      4 Mike                                            34

创建如下多表视图：

create view v_stu_sco
as 
select stu.sno, stu.sname, stu.sage, sco.score
from student stu left join score sco
on stu.sno = sco.student_no;

视图的查询结果如下所示：

SQL> select * from v_stu_sco;

                                    SNO SNAME                                         SAGE SCORE
--------------------------------------- ---------- --------------------------------------- -----
                                      1 Tom                                             31    56
                                      2 Kite                                            32    82
                                      3 Bob                                             33    90
                                      4 Mike                                            34 
                                      5 lisi                                            -6

由于多表视图无法直接更新，我们可以使用 instead of 触发器来实现。示例如下：

create or replace trigger upd_stu_sco_view
instead of update on v_stu_sco for each row
begin
    update score set score = :new.score where student_no = :new.sno;
    dbms_output.put_line('已激活触发器~');
end;
/

执行如下更新语句，将学号为 3 的学生的成绩由 90 变为 100，具体如下：

1	update v_stu_sco set score = 100 where sno = 3;

执行结果如下所示：

SQL>  update v_stu_sco set score = 100 where sno = 3;

已激活触发器~

1 row updated


SQL> select * from score;

STUDENT_NO SCORE
---------- -----
         1    56
         2    82
         3   100 -- 发生更新

5. 模式触发器

模式触发器也叫 DDL 触发器，即再对数据库对象进行操作的时所触发执行的触发器，示例如下：

CREATE OR REPLACE TRIGGER log_drop_obj
AFTER DROP ON SCHEMA
BEGIN
  INSERT INTO dropped_obj
  VALUES( ORA_DICT_OBJ_NAME, 
          ORA_DICT_OBJ_TYPE, SYSDATE);
END;
/

这里需要说明的是 ORA_DICT_OBJ_NAME 和 ORA_DICT_OBJ_TYPE 是 oracle 的系统变量，常用的系统变量如下所示：

Ora_client_ip_address 返回客户端的ip地址
Ora_database_name 返回当前数据库名
Ora_login_user 返回登录用户名
Ora_dict_obj_name 返回ddl操作所对应的数据库对象名
Ora_dict_obj_type 返回ddl操作所对应的数据库对象的类型

首先我们需要创建dropped_obj 表用于保存记录，创建语句如下所示：

CREATE TABLE dropped_obj (
obj_name VARCHAR2(30),   
obj_type VARCHAR2(20),
drop_date DATE);

再创建上述的模式触发器之后，我们把 score 删除，dropped_obj 表中的记录如下所示：

SQL> select * from dropped_obj;

OBJ_NAME                       OBJ_TYPE             DROP_DATE
------------------------------ -------------------- -----------
SCORE                          TABLE                2020/11/4 2

6. 数据库级触发器

数据库级触发器在发生打开、关闭、登录和退出数据库等系统事件时执行。下面通过示例说明，首先创建 log_table 表：

1	create table log_table(username varchar2(20), logon_time date, logoff_time date, address varchar2(20));

接着创建如下触发器：

create or replace trigger tr_logon
after logon on database -- 登录数据库后触发
begin 
   insert into log_table(username,logon_time, address ) values(ora_login_user,  sysdate, ora_client_ip_address);
end;
/

在登录用户之后，会往 log_table 插入一条记录，如下所示：

SQL> select * from log_table;

USERNAME             LOGON_TIME  LOGOFF_TIME ADDRESS
-------------------- ----------- ----------- --------------------
SYSTEM               2020/11/4 2

7. 启用和禁用

示例如下所示：

1	SQL> ALTER TRIGGER aiu_itemfile DISABLE;

1	SQL> ALTER TRIGGER aiu_itemfile ENABLE;

8. 删除

示例如下所示：

1	SQL> DROP TRIGGER aiu_itemfile;

9. 触发器信息

USER_TRIGGERS 数据字典视图包含有关触发器的信息，查询示例如下所示：

SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS
WHERE TABLE_NAME='EMP';

SQL> SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE
FROM USER_TRIGGERS
WHERE TRIGGER_NAME = 'BIU_EMP_DEPTNO';