Oracle触发器

今天药忘吃喽~ 2021-09-12 08:01 434阅读 0赞

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

# 1、触发器概述 #

在触发器中有一个不得不提的概念——触发事件，触发器正是通过这个“触发事件”来执行的。能够引起触发器运行的操作被称为“触发事件”，如执行DML语句（使用INSERT、UPDATE、DELETE语句对表或视图执行数据处理操作）；执行DDL语句（使用CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）；引发数据库系统事件（如系统启动或退出、产生异常错误等）；引发用户事件（如登录或退出数据库操作），以上这些操作都可以引起触发器的运行。

触发器的语法格式：

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

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

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

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

tri\_name：触发器的名称，如果数据库中已经存在了此名称，则可以指定“or replace”关键字，这样新的触发器将覆盖掉原来的触发器。

tri\_event：触发事件，比如INSERT、UPDATE、DELETE、CREATE、ALTER、DROP等。

table\_name | view\_name | user\_name |db\_name：分别表示操作的数据表、视图、用户模式和数据库，对它们的某些操作将引起触发器的运行。

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

plsql\_sentences：PL/SQL语句，它是触发器功能实现的主体。

Oracle所支持的触发器分为以下5种类型：

行级触发器：当DML语句对每一行数据进行操作时都会引起该触发器的运行。

语句级触发器：无论DML语句影响多少行数据，其所引起的触发器仅执行一次。

替换触发器：该触发器使定义在视图上的，而不是定义在表上，它是用来替换所使用实际语句的触发器。

用户事件触发器：是指与DDL操作或用户登录、退出数据库等事件相关的触发器。如，用户登录到数据库或使用ALTER语句修改表结构等。

系统事件触发器：是指在Oracle数据库系统的事件中进行触发的触发器，如Oracle实例的启动或关闭。

# 2、语句级触发器 #

语句级触发器，顾名思义，就是针对一条DML语句而引起的触发器执行。在语句级触发器中，不使用for each row子句，也就是说无论无论数据操作影响多少行，触发器都只会执行一次。下面就通过一系列连续的例子来看一下创建和引发一个语句级触发器的实现过程。

**【实例】**使用触发器在SCOTT模式下针对dept表的各种操作进行监控，为此首先需要创建一个日志表dept\_log，它用于存储对dept表的各种数据操作信息，比如操作种类（如插入、修改、删除操作）、操作时间等。

（1）在SCOTT模式下创建dept\_log数据表，并在其中定义两个字段，分别用来存储操作种类信息和操作时间，代码如下。

create table dept_log
    (
      operate_tag varchar2(10),  --定义字段，存储操作种类信息
      operate_time date          --定义字段，存储操作日期
    );

（2）创建一个触发器tri\_dept，该触发器在insert、update和delete事件下都可以被触发，并且操作的数据对象是dept表，要求在触发器执行时输出对dept表所做的具体操作，代码如下。

create or replace trigger tri_dept
      before insert or update or delete
      on dept --创建触发器，当dept表发生插入，修改，删除操作时引起该触发器执行
    declare
      var_tag varchar2(10);--声明一个变量，存储对dept表执行的操作类型
    begin
      if inserting then --当触发事件是INSERT时
        var_tag := '插入';--标识插入操作
      elsif updating then --当触发事件是UPDATE时
        var_tag := '修改';--标识修改操作
      elsif deleting then--当触发事件是DELETE时
        var_tag := '删除';--标识删除操作
      end if;
      insert into dept_log
      values(var_tag,sysdate);--向日志表中插入对dept表的操作信息
    end tri_dept;

在上面的代码中，使用before关键字来指定触发器的“触发时机”，它指定当前的触发器在DML语句执行之前被触发，这使得它非常适合于强化安全性、启用业务逻辑和进行日志信息记录。当然也可以使用after关键字，它通常用于记录该操作或者做某些事后处理工作。

另外，对于条件谓词，用户甚至还可以在其中判断特定列是否被更新。例如，用户是否对dept表中的dname列进行了修改，可以使用下面的语句：

if updating(dname) then --若修改了dept表中的dname列
      do something about update dname
    end if;

在上面的条件谓词中，即使用户修改了dept表中的数据，但却没有对dname列的值进行修改，那么条件谓词的值仍然为false，这样相关的do something语句就不会得到执行。

（3）在数据表dept中实现插入、修改、删除3中操作，以便引起触发器tri\_dept的执行，代码如下。

insert into dept values(66,'业务咨询部','深圳');
    
    update dept set loc='北京' where deptno=66;
    
    delete from dept where deptno=66;

（4）使用select语句查看dept\_log日志信息表，代码如下。

select * from dept_log;

# 3、行级触发器 #

不言而喻，行级触发器会针对DML操作所影响的每一行数据都执行一次触发器。创建这种触发器时，必须在语法中使用for each row。使用行级触发器的一个典型应用就是给数据表生成主键值，下面就来讲解这个典型应用的实现过程。

**【实例】**使用行级触发器生成数据表中的主键值。

（1）在SCOTT模式下，创建一个用于存储商品种类的数据表，其中包括商品序号列和商品名称列，代码如下。

create table goods
    (
      id int primary key,
      good_name varchar2(50)
    );

（2）使用create sequence语句创建一个序列，命名为seq\_id，代码如下。

create sequence seq_id;

（3）创建一个行级触发器，该触发器在数据表goods插入数据时被触发，并且在该触发器的主体中实现设置goods表的id列的值，代码如下。

create or replace trigger tri_insert_good
      before insert
      on goods --关于goods数据表的id，在插入id列之前，引起该触发器的运行
      for each row --创建行级触发器
    begin
      select seq_id.nextval
      into :new.id
      from dual;--从序列中生成一个新的数值，赋值给当前插入行的id列
    end;

在上面的代码中，为了创建行级触发器，使用for each row；为了给goods表的当前插入行的id列赋值，这里使用了“:new.id”关键字——列标识符，这个列标识符用来指导新行的id列，给它赋值，就相当于给当前行的id列赋值。

在行级触发器中，可以访问当前正在受到影响（添加、删除、修改等操作）的数据行，这就可以通过列标识符来实现。列标识符可以分为“原值标识符”和“新值标识符”，原值标识符用于标识当前行某个列的原始值，记作“:old.column\_name”（如，:old.id），通常在update语句和delete语句中使用，因为在insert语句中新插入的行没有原始值；新值标识符用于当前行某个列的新值，记作“:new.column\_name”（如，:new.id），通常在insert语句和update语句中使用，因为delete语句中被删除的行无法产生新值。

（4）向goods表中插入一条记录，代码如下。

insert into goods(good_name) values('苹果');

（5）使用select语句检索goods表，查看结果，代码如下。

select * from goods;

# 4、替换触发器 #

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

【实例】创建并使用替换触发器。

（1）在SYSTEM模式下，个scott用户授予“create view”（创建视图）权限，然后在SCOTT模式下创建一个检索雇员信息的视图，该视图的基表包括dept表和emp表，代码如下。

create view view_emp_dept
      as select empno,ename,dept.deptno,dname,job,hiredate
         from emp,dept
         where emp.deptno = dept.deptno;

（2）创建一个关于view\_emp\_dept的视图替换触发器，在该触发器的主体中实现向emp表和dept表中插入两行相互关联的数据，代码如下。

create or replace trigger tri_insert_view
      instead of insert
      on view_emp_dept--创建一个关于view_emp_dept视图的替换触发器
      for each row--是行级视图
    declare
      row_dept dept%rowtype;
    begin
      select * into row_dept from dept where deptno = :new.deptno;--检索指定部门编号的记录行
      if sql%notfound then--未检索到该部门编号的记录
         insert into dept(deptno,dname)
         values(:new.deptno,:new.dname);--向dept表中插入数据
      end if;
      insert into emp(empno,ename,deptno,job,hiredate)
      values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate);--向emp表中插入数据
    end tri_insert_view;

# 5、用户事件触发器 #

用户事件触发器是因进行DDL操作或用户登录、退出等操作而引起运行的一种触发器，引起该类型触发器运行的常见用户事件包括：CREATE、ALTER、DROP、ANALYZE、COMMENT、GRANT、REVOKE、RENAME、TRUNCATE、SUSPEND、LOGON和LOGOFF等。

**【实例】**创建和使用用户事件触发器。

（1）使用create table语句创建一个日志信息表，该表保存的日志信息包括数据对象，数据对象类型、操作行为、操作用户和操作日志等，代码如下。

create table ddl_oper_log
    (
      db_obj_name varchar2(20),--数据对象名称
      db_obj_type varchar2(20),--对象类型
      oper_action varchar2(20),--具体dll行为
      oper_user varchar2(20),--操作用户
      oper_date date--操作日期
    );

（2）关于scott用户的DDL操作（这里包括CREATE、ALTER和DROP）创建一个触发器，然后将DDL操作的相关信息插入到ddl\_oper\_log日志表中，代码如下。

create or replace trigger tri_ddl_oper
      before create or alter or drop
      on scott.schema --在scott模式下，在创建、修改、删除数据对象之前将引发该触发器运行
    begin
      insert into ddl_oper_log values(
        ora_dict_obj_name,--操作的数据对象名称
        ora_dict_obj_type,--对象类型
        ora_sysevent,--系统事件名称
        ora_login_user,--登录用户
        sysdate);
    end;

上面代码中，当向日志表ddl\_oper\_log插入数据时，使用了若干个事件属性，它们各自的含义如下：

ora\_dict\_obj\_name：获取DDL操作所对应的数据库对象。

ora\_dict\_obj\_type：获取DDL操作所对应的数据库对象的类型。

ora\_sysevent：获取触发器的系统事件名。

ora\_login\_user：获取登录用户名。

（3）在SCOTT模式下，创建一个数据表和一个视图，然后删除视图和修改数据表，最后使用select语句查看ddl\_oper\_log日志表中的DDL操作信息，代码如下。

create table tb_test(id number);
    
    create view view_test as select empno,ename from emp;
    
    alter table tb_test add(name varchar2(10));
    
    drop view view_test;
    
    select * from ddl_oper_log;