.Net程序员学用Oracle系列(25)：触发器详解

1、触发器理论
2、触发器实战
3、总结

1、触发器理论

1.1、触发器的应用场景

在 Oracle 中，事件或语句都能触发触发器，这也正是触发器的用武之地，具体来说有以下 4 类可以触发触发器的语句或事件：

1、DML 语句，用于修改特定表或视图中数据的 INSERT、UPDATE、DELETE 等语句。
2、DDL 语句，主要是 CREATE、ALERT 等常见 DDL 语句。
3、系统事件，如启动或关闭数据库实例、错误消息等。
4、用户事件，如登录或注销。

触发器通常用来执行复杂的业务规则，用于实现由数据库完整性约束难以实现的约束，或用来自动生成分配列值、维护表数据同步、在分布式数据库中的节点之间强制引用完整性、防止无效操作、提供审计、监控对数据库的各种操作、提供透明的事件记录、当对视图发出 DML 语句时修改表数据等。

在项目中应尽可能少的使用触发器，因为过多使用触发器可能会导致复杂的相互依赖关系，这在很大的应用程序中是难以维护的。例如，当某个触发器被触发后，该触发器中的 SQL 语句又触发了其它的触发器，也就是级联触发，这可能会导致一些无法预见的影响。

尽管可以同时使用触发器和完整性约束来定义实施任何类型的完整性规则。但 Oracle 强烈建议仅在以下两种情况下使用触发器来约束数据输入：

1、执行使用完整性约束难以实现的复杂业务规则。
2、当子表和父表位于分布式数据库的不同节点时，执行引用完整性。

1.2、触发器的类型

在 Oracle 的官方手册中将触发器分为 4 类，分别是：行级触发器和语句级触发器、BEFORE 触发器和 AFTER 触发器、INSTEAD OF 触发器、事件触发器。另外，行级触发器和语句级触发器与 BEFORE 触发器和 AFTER 触发器还可以两两组合形成 4 种复合型的 DML 触发器。

1、行级触发器和语句级触发器

定义触发器时可以指定触发条件和触发频次。触发器条件是指由 WHEN 子句指定的一个逻辑表达式，当该表达式结果为 TRUE 时才会执行触发器。行级触发器的运行次数与受影响的行数相关，而语句级触发器在被触发后只运行一次。

行级触发器：触发语句每影响一行触发一次行级触发器。例如，UPDATE 表中多个行，则会对 UPDATE 语句影响的每一行触发一次行级触发器。如果触发语句不影响行，则不会运行行级触发器。如果触发操作中的代码取决于触发语句或需要访问受影响行的数据，则行级触发器很有用。

语句级触发器：无论触发语句会影响表中多少行，即使没有行受到影响，语句级触发器也会被触发一次。例如，DELETE 表中多个行，则会对 DELETE 语句影响的表触发一次语句级触发器。如果触发器操作中的代码不依赖于触发语句和受影响的行的数据，则语句级触发器很有用。

2、BEFORE 触发器和 AFTER 触发器

定义触发器时可以指定触发时机，具体来说就是可以指定是在触发事件之前还是之后执行。DML 语句触发的 BEFORE 触发器和 AFTER 触发器只能在表上定义，不能在视图上定义。如果用户执行了针对视图的 DML 语句，则会触发视图基表上的触发器。DDL 语句触发的 BEFORE 触发器和 AFTER 触发器只能在数据库或模式上定义，而不能在特定的表上定义。

BEFORE 触发器：在触发语句运行之前运行触发器操作。当需要根据情况确定是否允许触发语句执行或需要在完成触发操作之前导出特定的数据时，BEFORE 触发器很有用。

AFTER 触发器：在触发语句运行之后运行触发器操作。AFTER 触发器主要用于执行那些必须在触发操作之后才能执行的操作。

3、INSTEAD OF 触发器

INSTEAD OF 触发器提供了一种透明的方式来修改不能通过 DML 语句直接修改的视图。这些触发器也被称为替代触发器，与其它类型触发器不同的是，替代触发器由 Oracle 触发而不是执行触发语句。可以针对视图编写正常的 DML 语句，并触发 INSTEAD OF 触发器适当的更新基表中的数据。

4、事件触发器

事件触发器可以被系统事件或用户事件触发。可以触发事件触发器的事件有：数据库的启动和关机、服务器错误消息事件、用户登录和注销、执行 DDL 语句、执行 DML 语句等。

1.3、DML 触发器的触发顺序

DML 触发器的类型比较多，当单个 SQL 语句触发了多个触发器时，Oracle 会按照既定的顺序依次运行每种类型的触发器。具体执行顺序如下：

1、执行 BEFORE 语句级的触发器。
2、遍历所有行受 DML 语句影响。
3、执行 BEFORE 行级的触发器。
4、锁定行，并执行触发触发器的 DML 语句，同时执行完整性约束检查，锁定在事务提交之前不能被释。
5、执行 AFTER 行级的触发器。
6、完成延迟的完整性约束检查。
7、执行 AFTER 语句级的触发器。

Oracle 针对不同类型的触发器在设定的时间点执行完整性约束检查，并保证触发器不会危及完整性约束。如果给定的语句触发了多个相同类型的触发器，Oracle 将以未指定的随机顺序触发多个触发器；也就是说，相同语句的相同类型的触发器不能保证以任何特定的顺序触发。所以不应该重复定义触发器。

2、触发器实战

2.1、创建触发器

可以在数据库级别或模式级别定义系统事件触发器。DDL 语句或登录/注销事件触发器也可以在数据库级别或模式级别进行定义。可以在表或视图上定义 DML 语句的触发器。在数据库级别定义的触发器对所有用户公开，并且只有当触发事件涉及该模式或表时，才会触发在模式或表级别定义的触发器。

在触发器中，允许访问触发事件的某些属性。例如，数据库启动和关闭触发器具有的实例编号和数据库名称，登录和注销触发器具有的用户名，DML 语句上的触发器具有的 OLD 列值和 NEW 列值等。

另外，在创建触发器时还需要注意以下事项：

1、即便在编译触发器期间发生了错误，仍然会创建触发器。所以创建触发器之后应该要再检查下触发器的状态，确定其是否真的可用。
2、触发器不能有参数。这个也好理解，因为不方便传参。
3、如果要在触发器中使用 SELECT 语句，那么只能是 SELECT ... INTO ... 结构或结合显示游标使用。简而言之，Oracle 触发器不支持标准的 SELECT 语句。
4、在触发器中默认可以直接用 DML 语句，但不能直接用 DDL 语句和事务控制语句。被触发器调用的存储过程也不能直接使用事务控制语句。因为触发器和触发语句在同一个事务中，当触发语句被提交或回退时，触发器也将被提交或回退。不注意这点可能会遇到一些莫名其妙的问题。
5、在触发器中不能使用 LONG 和 LONG RAW 类型，可以使用 LOB 类型，但不可以修改 LOB 类型的列值。
6、定义触发器的代码不能超过 32K。如果触发逻辑比较复杂的话，应将大部分代码放到存储过程中，再从触发器中调用存储过程。

2.1.1、创建 DML 触发器

语法：

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} -- 指定触发时机
{INSERT | DELETE | UPDATE [OF column1 [,column2 ...]]} -- 指定触发事件
ON [SCHEMA.]table_name -- 指定触发对象
[REFERENCES {OLD [AS] old_name | NEW [AS] new_name | PARENT [AS] parent_name}]
[FOR EACH ROW] -- 指定为行级触发器，不指定默认为语句级触发器
[WHEN condition] -- 指定触发条件，只有行级触发器才能指定触发条件
DECLARE
  -- 定义变量
BEGIN
  -- 触发器操作代码
EXCEPTION
  WHEN ...
  -- 异常处理代码
END [trigger_name];

BEFORE INSERT 触发器

示例：

CREATE OR REPLACE TRIGGER trg_bi
BEFORE INSERT ON demo.t_course
FOR EACH ROW
DECLARE
  v_today DATE;
BEGIN
  v_today := SYSDATE;
  :NEW.course_desc := '创建于 '||TO_CHAR(SYSDATE,'yyyy-mm-dd')||'。'||:NEW.course_desc;
END;

测试：

INSERT INTO demo.t_course VALUES(6,'计算机英语','计算机专业英语课程');

BEFORE UPDATE 触发器

示例：

CREATE OR REPLACE TRIGGER trg_bu
BEFORE UPDATE ON demo.t_course
FOR EACH ROW
DECLARE
  v_today DATE;
BEGIN
  v_today := SYSDATE;
  IF :NEW.course_name != :OLD.course_name THEN
    :NEW.course_desc := '修改于 '||TO_CHAR(SYSDATE,'yyyy-mm-dd')||'。'||:NEW.course_desc;
  END IF;
END;

测试：

UPDATE demo.t_course t SET t.course_name='计算机英语（下册）' WHERE t.course_id=6;

BEFORE DELETE 触发器

示例：

CREATE OR REPLACE TRIGGER trg_bd
BEFORE DELETE ON demo.t_course
FOR EACH ROW -- FOR EACH ROW 必须要在 WHEN 字句之前，否则无法创建触发器
WHEN (OLD.course_id > 0) -- 此处 OLD 前面不能带冒号，与 PL/SQL 块中额写法相反
DECLARE
  v_today DATE;
  v_username VARCHAR2(20);
BEGIN
  v_today := SYSDATE;
  SELECT USER INTO v_username FROM DUAL;
  
  -- 将正式的课程数据备份到备份表
  INSERT INTO t_course_backup(course_id, course_name, course_desc, delete_date, deleted_by)
  VALUES(:OLD.course_id, :OLD.course_name, :OLD.course_desc, v_today, v_username);
END;

测试：

DELETE FROM demo.t_course t WHERE t.course_id=6;

AFTER INSERT & UPDATE & DELETE 触发器

示例：

CREATE OR REPLACE TRIGGER trg_aiud
AFTER INSERT OR UPDATE OR DELETE ON demo.t_course
FOR EACH ROW
DECLARE
  v_today DATE;
  v_username VARCHAR2(20);
BEGIN
  v_today := SYSDATE;
  SELECT USER INTO v_username FROM DUAL;
  
  -- 根据不同触发语句，执行不同备份操作
  IF INSERTING THEN -- 当添加事件被触发时
    INSERT INTO t_course_backup(course_id, course_name, course_desc, insert_date, inserted_by)
    VALUES(:NEW.course_id, :NEW.course_name, :NEW.course_desc, v_today, v_username);
  ELSIF UPDATING THEN -- 当修改事件被触发时
    INSERT INTO t_course_backup(course_id, course_name, course_desc, update_date, updated_by)
    VALUES(:OLD.course_id, :OLD.course_name, :OLD.course_desc, v_today, v_username);
  ELSIF DELETING THEN -- 当删除事件被触发时
    INSERT INTO t_course_backup(course_id, course_name, course_desc, delete_date, deleted_by)
    VALUES(:OLD.course_id, :OLD.course_name, :OLD.course_desc, v_today, v_username);
  END IF;
END;

测试：

INSERT INTO demo.t_course VALUES(8,'微积分','高等数学');
UPDATE demo.t_course t SET t.course_name='微积分入门' WHERE t.course_id=8;
DELETE FROM demo.t_course t WHERE t.course_id=8;

2.1.2、创建 DDL 触发器

示例：

CREATE OR REPLACE TRIGGER trg_ddl_oper
/**************************************************
功能：监控数据库的 DDL 操作，阻止所有针对 demo 的 DLL 操作（此处仅为示例，无实际意义）

修订记录：
版本号    修订时间      修订人    描述
1.0.0     2017-05-12    hanzz     1.创建此触发器
**************************************************/
BEFORE DDL ON demo.SCHEMA -- 还可以指定为某一种 DDL 或 DATABASE
DECLARE
  event VARCHAR2(30);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  event := ORA_SYSEVENT; -- 接收触发事件
  
  INSERT INTO demo.t_ddl_log(trg_event, obj_owner, obj_name, user_name, attempt_time)
  SELECT event,ORA_DICT_OBJ_OWNER,ORA_DICT_OBJ_NAME,USER,SYSTIMESTAMP FROM DUAL;
  COMMIT;
  
  IF event = 'CREATE' THEN
    RAISE_APPLICATION_ERROR(-20001,'禁止创建对象！');
  ELSIF event = 'DROP' THEN
    RAISE_APPLICATION_ERROR(-20002,'禁止删除对象！');
  ELSIF event = 'TRUNCATE' THEN
    RAISE_APPLICATION_ERROR(-20003,'禁止清空表！');
  ELSIF event = 'COMMENT' THEN
    RAISE_APPLICATION_ERROR(-20004,'禁止修改注释！');
  ELSE
    RAISE_APPLICATION_ERROR(-20001,'禁止 '||ORA_SYSEVENT||' 操作！');
  END IF;
END;

2.1.3、创建事件触发器

1、禁止通过 PL/SQL Developer 来登录 SCOTT 用户

CREATE OR REPLACE TRIGGER trg_deny_plsqldev
AFTER LOGON ON DATABASE
DECLARE
  v_program VARCHAR2(50);
  v_username VARCHAR2(50);
BEGIN
  SELECT t.program,t.username INTO v_program,v_username FROM V$SESSION t 
  WHERE t.audsid=SYS_CONTEXT('USERENV','SESSIONID') AND ROWNUM=1;
  
  IF LOWER(v_program)='plsqldev.exe' AND v_username='SCOTT' THEN
    RAISE_APPLICATION_ERROR(-20000,'It is forbidden to login SCOTT via PL/SQL Developer!');
  END IF;
END;

2、限制指定用户和指定 IP 登录

CREATE OR REPLACE TRIGGER trg_deny_login
AFTER LOGON ON DATABASE
DECLARE
  v_ip VARCHAR2(20);
  v_message VARCHAR2(500);
  v_deny_user_exception EXCEPTION;
  v_deny_ip_exception EXCEPTION;
BEGIN
  v_ip := SYS_CONTEXT('USERENV','IP_ADDRESS');
  INSERT INTO demo.t_login_his(user_name, login_time, login_ip) VALUES(USER,SYSTIMESTAMP,v_ip);
  COMMIT;
  
  IF USER IN('SCOTT','DEMO') THEN
    RAISE v_deny_user_exception;
  END IF;
  
  IF v_ip = '127.0.0.1' THEN
    RAISE v_deny_ip_exception;
  END IF;
  
  EXCEPTION
    WHEN v_deny_user_exception THEN
      v_message := '禁止使用 SCOTT 等用户登录数据库！';
      RAISE_APPLICATION_ERROR(-20001,v_message);
    WHEN v_deny_ip_exception THEN
      v_message := '禁止在 127.0.0.1 上登录数据库！';
      RAISE_APPLICATION_ERROR(-20001,v_message);
END;

上面两个限制登录的触发器设计思路都是：捕获到拒绝登录的用户、IP 或应用程序就抛出应用异常强制用户退出登录。因此它们的缺陷也是一样的，首先，如果用户具有 DBA 的权限，触发器就不起作用了；其次，如果用户通过 SQLPlus 登录，SYS_CONTEXT('USERENV','IP_ADDRESS')就会返回空。

2.2、调试触发器

在 PL/SQL Developer 中，调试存储过程的功能还蛮好用的，直接在存储过程名称上右键，然后点测试即可进入调试窗口，接下来就可以调试了。而触发器似乎是不能调试的，因为触发器的右键菜单中没有测试选项，其实不然！根本原因在于触发器和存储过程的调用方式不同，用户是无法直接调用触发器的，只能由 Oracle 系统来调用，但用户可以模拟触发的动作，让 Oracle 系统自动去调用。

下面就拿trg_aiud来举例说明该如何调试触发器：

第一步：在触发器名称上右键，点击添加调试信息。注意：这一步非常重要，如果不添加调试信息，就不能单步调试了，这样也就没有调试的意义了。
第二步：新建一个测试窗口，在测试窗口中写出能够触发触发器的语句。且要以 PL/SQL 块的形式，即将语句写在 BEGIN ... END 之间。
第三步：按F9或者点击开始调试器，即可进入调试模式。接下来你可以按Ctrl+N单步进入或Ctrl+O单步跳过，需要的话也可以Ctrl+R运行到底。当然也可以点调试窗口上面的那几个菜单按钮，事实上我一般都是点按钮（因为 PL/SQL Developer 的快捷键实在太不好用了）。

2.3、禁用和启用触发器

ALTER TRIGGER trg_aiud DISABLE; -- 禁用名为 trg_aiud 的触发器
ALTER TRIGGER trg_aiud ENABLE;  -- 启用名为 trg_aiud 的触发器
ALTER TABLE t_course DISABLE ALL TRIGGERS; -- 禁用 t_course 表上所有的触发器
ALTER TABLE t_course ENABLE ALL TRIGGERS;  -- 启用 t_course 表上所有的触发器

3、总结

本文从理论和实战两个方面讲述了 Oracle 触发器的方方面面，唯独没有涉及替代触发器，只因前文《.Net程序员学用Oracle系列(23)：视图理论、物化视图：可更新的连接视图》中已经讲过。最后，我再重申一遍，除非是某些特殊情况，否则尽可能不用触发器，因为用多了可能会导致项目难以维护。

本文链接：http://www.cnblogs.com/hanzongze/p/oracle-trigger.html
版权声明：本文为博客园博主 韩宗泽 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！

posted @ 2017-05-15 09:26 韩宗泽阅读(1086) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Ace Han

Think Bigger! Build Smarter!