Oralce中的触发器

84 阅读 0 评论 56 点赞

我是靠谱客的博主魁梧外套，最近开发中收集的这篇文章主要介绍Oralce中的触发器，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1、创建一个触发器

CREATE [OR REPLACE] TRIGGER tri_name
  [BEFORE | AFTER | INSTEAD OF] tri_event
  ON table_name | view_name | user_name | db_name
  [FOR EACH ROW] [WHEN tri_condition]
BEGIN
  plsql_sentence;
END tri_name;

关键字说明：
trigger：表示创建触发器的关键字

before | after | instead of：表示“触发时机”的关键字。before表示在执行DML操作之前触发，这种方式能够方式某些错误操作发生而便于回滚或实现某些业务规则。after表示在DML等操作之后发生，这种方式便于记录或做某些时候处理信息。instead of表示触发器为替代触发器。

on：表示操作的数据表、视图、用户模式和数据库等，对他们执行某种数据操作（比如对表执行insert,alter,drop等操作），将引起触发器的运行。

for each row：指定触发器为行级触发器，当DML语句对每一行数据进行操作时都会引起该触发器的运行。如果未指定该条件，则表示创建语句级触发器，这时无论数据操作影响多少行，触发器都只会执行一次。

参数说明：

tri_name：触发器的名称

tri_event：触发事件，常用的有：insert,update,delete,create,alter,drop等

table_name | view_name | user_name | db_name：分别表示操作的数据表，视图，用户模式和数据库，对他们的某些操作将引起触发器的运行

tri_condition：表示触发条件表达式，只有当该表达式的值为true时，遇到触发事件才会自动执行触发器，使其执行触发操作，否则即便是遇到触发事件也不会执行触发器

plsql_sentence：PL/SQL语句，是除非功能实现的主体

1.1、语句级触发器

语句级触发器就是针对一条DML语句而引起的触发器执行。在语句级触发器中，不使用for each row子句，也就是说无论数据操作影响多少行，触发器都只会执行一次

CREATE OR REPLACE TRIGGER tri_dept
  BEFORE INSERT OR UPDATE OR DELETE 
  ON dept
DECLARE
  var_tag VARCHAR2(10);
BEGIN
  IF inserting THEN
    var_tag := '插入';
  ELSIF updating THEN
    var_tag := '修改';
  ELSIF deleting THEN
    var_tag := '删除';
  END IF;
  INSERT INTO dept_log VALUES (var_tag,sysdate);
END tri_dept;
/

用户也可以在其中判断特定列是否被更新

IF updating(dname) THEN 
  do something about update;

1.2、行级触发器

行级触发器会针对DML操作所影响的每一行数据都执行一次触发器。创建这种触发器时，必须在语法中使用for each row。行级触发器最典型的应用就是给数据表生成主键值

CREATE OR REPLACE TRIGGER tri_insert_good
  BEFORE INSERT ON goods
  FOR EACH ROW
BEGIN
  SELECT seq_id.nextval INTO :new.id FROM dual;
END;
/

在行级触发器中，可以访问当前正在受到影响（添加，删除，修改等操作）的数据行，这就可以通过列标识符来实现。列标识符可以分为“原值标识符”和“新值标识符”，原值标识符用于表示当前行某个列的原始值（“old.column_name”，通常在update语句和delete语句中使用），新值标识符表示当前行某个列的新值（“new.column_name”，通常在update和insert语句中使用）

1.3、替换触发器

替换触发器是定义在视图上的，而不是定义在表上。由于视图是由多个基表连接组成的逻辑结构，所以一般不允许用户进行DML操作，这样当用户为视图编写“替换触发器”后，用户对视图的DML操作实际就变成了执行触发器中的PL/SQL语句块，这样就可以通过在“替换触发器”中编写适当的代码对构成视图的各个基表进行操作。

CREATE OR REPLACE TRIGGER tri_insert_view
  INSTEAD OF INSERT ON view_emp_dept FOR EACH ROW
DECLARE
  row_dept dept.deptno%type;
BEGIN
  INSERT INTO emp (empno,ename,deptno) VALUES (:new.empno,:new.ename,:new.deptno);
  SELECT deptno INTO row_dept FROM dept d WHERE d.deptno = :new.deptno;
  EXCEPTION 
    WHEN no_data_found THEN
     INSERT INTO dept (deptno,dname) VALUES (:new.deptno,:new.dname);
END tri_insert_view;
/

1.4、用户事件触发器

用户事件触发器是因为进行DML操作或用户登录、退出等操作而引起的一种触发器，引起该类型触发器运行的用户事件包括：create ,alter,drop,analyze,comment,grant,revoke,rename,truncate,suspend,logon和logoff等

CREATE OR REPLACE TRIGGER tri_ddl_oper
  BEFORE CREATE OR ALTER OR DROP ON system.schema
BEGIN
  INSERT INTO ddl_oper_log VALUES(
    ora_dict_obj_name, --操作的数据对象名称
    ora_dict_obj_type, --操作的数据对象类型
    ora_sysevent,      --系统时间名称
    ora_login_user,    --登录用户
    sysdate
  );
END;
/

事件属性说明：

ora_dict_obj_name：操作的数据对象名称
ora_dict_obj_type：操作的数据对象类型
ora_sysevent：系统时间名称
ora_login_user：登录用户

总结：

触发器可以看做一种“特殊”的存储过程，它定义了一些与数据库相关事件（如：insert,update,create等事件）发生时应执行的“功能代码块”，通常用于管理复杂的完整性约束，或监控对表的修改，或通知其他程序，甚至可以实现对数据的审计功能