触发器整理

浅浅的花香味﹌ 2022-06-16 06:08 259阅读 0赞

# 一、概述 #

## **1.1 触发器概述\*\*** ##

触发器是在事件发生时隐式地自动运行的PL/SQL程序块，不能接收参数，不能被调用。它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如:当对一个表进行操作（ insert，delete， update）时就可以激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器是许多关系数据库系统都提供的一项技术。

触发器与储存过程:

都有声明，执行和异常处理过程的PL/SQL块。触发器可以调用存储过程。我的一般理解就是触发器是一个隐藏的存储过程，因为它不需要参数，不需要显示调用。**\*\***

## **1.2 触发器组成:** ##

触发事件：引起触发器被触发的事件。 例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。既可以是单个触发事件，也可以是多个触发事件的组合（多个触发事件组合只能使用OR逻辑组合，不能使用AND逻辑组合）。

触发时间：即该触发器在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该触发器的操作顺序。触发操作：即该触发器被触发之后的操作，正是触发器本身要做的事情。 例如：PL/SQL 块。触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不同的处理，需要使用ORACLE提供的如下条件谓词。

*  INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。  
 *  UPDATING*\*\[（\**column*\_1,column\_*2,…,column*\_x）\]：当触发事件是UPDATE时，如果修改了column\_*x列，则取值为TRUE，否则为FALSE。其中column\_x是可选的。  
 *  DELETING：当触发事件是DELETE时，则取值为TRUE，否则为FALSE。触发条件：由WHEN子句指定一个逻辑表达式，只允许在行级触发器上指定触发条件，指定UPDATING后面的列的列表

**\*\*1.3 触发器触发次序\*\***

1.执行 BEFORE语句级触发器;

2.对与受语句影响的每一行：

> 执行 BEFORE行级触发器

> 执行 DML语句

> 执行 AFTER行级触发器

3.执行 AFTER语句级触发器

# 二、语法 #

## **2.1 触发器创建语法** ##

创建触发器的一般语法是:

CREATE \[OR REPLACE\] TRIGGER trigger\_name

> TRIGGERING\_EVENT
> 
> \{INSERT | DELETE | UPDATE \[OF column \[, column …\]\]\}
> 
> \[OR \{INSERT | DELETE | UPDATE \[OF column \[, column …\]\]\}...\]
> 
> ON \[schema.\]table*\_name | \[schema.\]view\_*name 
> 
> \[REFERENCING \{OLD \[AS\] old | NEW \[AS\] new| PARENT as parent\}\]
> 
> \[FOR EACH ROW \]--行级触发

\[WHEN condition\]\--限制条件

PL/SQL*\_BLOCK | CALL procedure\_*name;

TRIGGERING\_EVENT表示激发触发器的事件（也可能包括特殊的表或视图），即BEFORE、AFTER、INSTEAD OF。

BEFORE和AFTER指出触发器的触发时序分别为前触发和后触发方式；前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。

INSTEAD OF 触发器则只能为行触发器。 INSTEAD OF 选项使ORACLE激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEAD OF触发器，而不能对表、模式和数据库建立INSTEAD OF 触发器。创建INSTEAD OF触发器需要注意以下几点：

1.  (1) 只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。
2.  (2) 不能指定BEFORE 或 AFTER选项。
3.  (3) FOR EACH ROW子可是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。
4.  (4) 没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建DML触发器就可以了。

FOR EACH ROW 选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操作影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，AFTER和BEFORE触发器为语句触发器

REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。

WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用INSTEAD OF 行触发器和其它类型的触发器中。当一个基表被修改( INSERT, UPDATE, DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。

**2.2删除、禁用或启用触发器**

### **删除触发器：** ###

drop trigger trigger\_name;

当删除其他用户模式中的触发器名称，需要具有drop any trigger 系统权限，当删除建立在数据库上的触发器时，用户需要具有administer database trigger 系统权限。此外，当删除表或视图时，建立在这些对象上的触发器也随之删除。

### **禁用或启用触发器：** ###

数据库trigger 的状态：

有效状态(enable)：当触发事件发生时，处于有效状态的数据库触发器trigger 将被触发。

无效状态(disable)：当触发事件发生时，处于无效状态的数据库触发器trigger 将不会被触发，此时就跟没有这个数据库触发器(trigger) 一样。

数据库trigger 的这两种状态可以互相转换。格式为：alter trigger trigger\_name \[disable | enable \];

例：alter trigger *\*emp\_view\_delete\** disable;

alter trigger语句一次只能改变一个触发器的状态，而alter table语句则一次能够改变与指定表相关的所有触发器的使用状态。格式为：

alter table \[schema.\]table\_name \{enable|disable\} all triggers;

例：使表EMP 上的所有TRIGGER 失效：

alter table *\*emp\** disable all triggers;

# 三、触发器分类 #

触发器必须由事件才能触发。触发器的触发事件分可为3类，分别是DML事件、DDL事件和数据库事件。 每类事件包含若干个事件，如下所示。数据库的事件是具体的，在创建触发器时要指明触发的事件。

## **3.1 DML事件(3种)** ##

INSERT 在表或视图中插入数据时触发

UPDATE 修改表或视图中的数据时触发

DELETE 在删除表或视图中的数据时触发

## **3.2 DDL事件(3种)** ##

CREATE 在创建新对象时触发

ALTER 修改数据库或数据库对象时触发

DROP 删除对象时触发

Oracle10g提供的系统事件触发器可以在DDL或数据库系统上被触发。

DDL指的是数据定义语言，如CREATE 、ALTER及DROP 等。

而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式(SCHEMA)之上时，只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器, 默认时为当前用户模式。当建立在数据库(DATABASE)之上时，该数据库所有用户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器时，要求用户具有ADMINISTER DATABASE TRIGGER权限。

下面给出系统触发器的种类和事件出现的时机（前或后）：

<table> 
 <tbody> 
 <tr> 
 <th>事件</th> 
 <th>允许的时机</th> 
 <th>说明</th> 
 </tr> 
 </tbody> 
 <tbody> 
 <tr> 
 <td>STARTUP</td> 
 <td>AFTER</td> 
 <td>启动数据库实例之后触发&nbsp;</td> 
 </tr> 
 <tr> 
 <td>SHUTDOWN</td> 
 <td>BEFORE</td> 
 <td>关闭数据库实例之前触发（非正常关闭不触发）</td> 
 </tr> 
 <tr> 
 <td>SERVERERROR</td> 
 <td>AFTER</td> 
 <td>数据库服务器发生错误之后触发</td> 
 </tr> 
 <tr> 
 <td>LOGON</td> 
 <td>AFTER</td> 
 <td>成功登录连接到数据库后触发</td> 
 </tr> 
 <tr> 
 <td>LOGOFF</td> 
 <td>BEFORE</td> 
 <td>开始断开数据库连接之前触发</td> 
 </tr> 
 <tr> 
 <td>CREATE </td> 
 <td>BEFORE，AFTER</td> 
 <td>在执行CREATE语句创建数据库对象之前、之后触发</td> 
 </tr> 
 <tr> 
 <td>DROP </td> 
 <td>BEFORE，AFTER</td> 
 <td>在执行DROP语句删除数据库对象之前、之后触发</td> 
 </tr> 
 <tr> 
 <td>ALTER</td> 
 <td>BEFORE，AFTER</td> 
 <td>在执行ALTER语句更新数据库对象之前、之后触发</td> 
 </tr> 
 <tr> 
 <td> DDL </td> 
 <td>BEFORE，AFTER</td> 
 <td>在执行大多数DDL语句之前、之后触发</td> 
 </tr> 
 <tr> 
 <td>GRANT</td> 
 <td>BEFORE，AFTER</td> 
 <td>执行GRANT语句授予权限之前、之后触发</td> 
 </tr> 
 <tr> 
 <td>REVOKE</td> 
 <td>BEFORE，AFTER</td> 
 <td>执行REVOKE语句收权限之前、之后触犯发</td> 
 </tr> 
 <tr> 
 <td>RENAME</td> 
 <td>BEFORE，AFTER</td> 
 <td>执行RENAME语句更改数据库对象名称之前、之后触犯发</td> 
 </tr> 
 <tr> 
 <td>AUDIT / NOAUDIT</td> 
 <td>BEFORE，AFTER</td> 
 <td>执行AUDIT或NOAUDIT进行审计或停止审计之前、之后触发</td> 
 </tr> 
 </tbody> 
</table>

## 3.3数据库事件(5种)   ##

STARTUP 数据打开时触发

SHUTDOWN 在使用NORMAL或IMMEDIATE选项关闭数据库时触发

LOGON 当用户连接到数据库并建立会话时触发

LOGOFF 当一个会话从数据库中断开时触发

SERVERERROR 发生服务器错误时触发

# 四、 DML触发器的限制 #

(1) 触发器不接受参数。

(2) 一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

(3) 触发器最大为32KB。(4) 若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

(5) 触发器体内的SELECT 语句只能为SELECT … INTO …结构，或者为定义游标所使用的SELECT 语句。

(6) 在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。

(7) 触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

(8) 在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能是表中的任何long和blob列。

(9) 不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。

# 五、触发器应用分析 #

触发器是维护数据库数据完整性的重要技术,通过对DBMS个性的分析,可以发现触发器使用的普遍规律,触发器通过对新旧数据更换的管理,可以保留历史性数据档案.数据库触发器应用技巧的好坏决定了应用程序的质量和效率.例如,对DBMS的维护技术和安全监控技术可以进行有益的补充和最直接的反映.

在设计数据库的时候也都会或多或少的使用存储过程和触发器，原因很简单：良好的性能，业务也好实现。

**触发器能实现如下功能：**

1、 允许/限制对表的修改

2、 自动生成派生列，比如自增字段

3、 强制数据一致

4、 提供审计和日志记录

5、 防止无效的事务处理

6、 启用复杂的业务逻辑

# 六、示例 #

## 例(一)    ##

限定不能对部门号为30的记录进行 行触发器操作。

--建立一个触发器, 当职工表 emp2 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。
    
    create table emp2 as select * from emp;
    
    create table emp_his as select * from emp2 where 1=2;
    
    create or replace trigger tri_del_emp
       before delete --指定触发时机为删除操作前触发
       on scott.emp2 
       for each row   --说明创建的是行级触发器 
    begin
       --将修改前数据插入到日志记录表 emp_his
       insert into emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
           values( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
    end;
    
    select * from emp_his;
    delete emp2 where empno=7788;
    
    drop trigger tri_del_emp;
    drop table emp2;
    drop table emp_his;

## 例(二) ##

限定不能对部门号为30的记录进行 行触发器操作。

--限定不能对部门号为30的记录进行 行触发器操作。
    create or replace trigger tri_limit_emp
    before update or insert or delete  --update、insert、delete 后面可以跟列名  update of empno,ename or delete...
    on scott.emp
    for each row
    when (old.deptno=30 or new.deptno=30)  
    begin   
        case         
             when updating then
                  raise_application_error(-20001,'不能修改emp表中，部门为30的员工信息');
             when deleting then
                  raise_application_error(-20002,'不能删除emp表中，部门为30的员工信息');
             when inserting then
                  raise_application_error(-20003,'不能向30号部门添加员工信息');
        end case;
    end;
    
    select * from emp;
    delete from emp where deptno=30;
    insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)values(1212,'name','job',7839,sysdate,450.00,200.00,30);
    update emp set job='CLERK' where deptno=30;
    drop trigger tri_limit_emp;

## 例(三) ##

当对tb\_user表执行DML语句时，将相关信息记录到日志表

--当对tb_user表执行DML语句时，将相关信息记录到日志表
    --创建测试表
    create table tb_user(
      u_id  number(5),
      u_name varchar2(40),
      u_age number(2),
      u_sex char(2)
    );
    --创建记录测试表
    create table user_log(
      ul_user  varchar2(50),
      ul_type  varchar2(50),
      ul_date  varchar2(50)
    );
     
    --创建触发器
    create or replace trigger tri_user
      after delete or insert or update
      on scott.tb_user
    declare
      v_type user_log.ul_type%type;
    begin
      if inserting then
        v_type := 'insert';
        Dbms_Output.put_line('记录已经成功插入，并已记录到日志');
      elsif updating then
        v_type := 'update';
        Dbms_Output.put_line('记录已经成功更新，并已记录到日志');
      elsif deleting then
        v_type := 'delete';
        Dbms_Output.put_line('记录已经成功删除，并已记录到日志');
      end if;
      insert into user_log(ul_user,ul_type,ul_date) values(user, v_type, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')); --USER表示当前用户名
    end;
    
    insert into tb_user(u_id,u_name,u_age,u_sex) values(7788,'张三',23,'男');
    commit;
    update tb_user set u_age = 30 where u_id = 7788;
    commit;
    delete tb_user where u_id = 7788;
    commit;
    
    select * from tb_user;
    select * from user_log;
     
    drop trigger tri_user;
    drop table user_log;
    drop table tb_user;

## 例(四) ##

限制对empc表修改（包括insert,delete,update）的时间范围，即不允许在非工作时间修改empc表

--限制对empc表修改（包括insert,delete,update）的时间范围，即不允许在非工作时间修改empc表。
    create or replace trigger tri_disable_use_emp
    before insert or update or delete
    on scott.emp
    begin        
        if (to_char(sysdate,'DAY') in ('星期六', '星期日')) or (to_char(sysdate, 'HH24:MI') not between '08:30' and '18:00') then      
          raise_application_error(-20001, '不是上班时间，不能修改emp表');      
        end if;
    end;
    delete emp where empno=7369;
    drop trigger tri_disable_use_emp;

## 例(五) ##

利用行触发器实现级联更新

--利用行触发器实现级联更新。在修改了主表dept中的deptno之后（after），级联的、自动的更新子表emp表中empno。
    create table deptc as select * from dept;
    create table empc as select * from emp;
    alter table deptc add constraint pk_deptc primary key (deptno);
    alter table empc add constraint pk_empc primary key(empno);
    alter table empc add constraint fk_empc foreign key (deptno) references deptc ( deptno );
    
    create or replace trigger tri_ref_deptc
    after update of deptno
    on scott.deptc
    for each row
    begin  
        dbms_output.put_line('旧的deptno值是'||:old.deptno||'、新的deptno值是'||:new.deptno);   
        update empc set deptno=:new.deptno where deptno=:old.deptno;
    end;
    
    update deptc set deptno=22 where deptno=20;
    
    select * from deptc;
    select * from empc;
    
    alter table empc drop constraint fk_empc;
    drop table deptc;
    drop table empc;

## 例(六) ##

在触发器中调用储存过程

--在触发器中调用储存过程
    --员工离职记录
    create table empc as select * from emp;
    create table emp_leave_record as select * from emp where 1=2;
    
    --创建储存过程
    create or replace procedure pro_leave_record( 
           v_empno emp_leave_record.empno%type,
           v_ename emp_leave_record.ename%type,
           v_job emp_leave_record.job%type,
           v_mgr emp_leave_record.mgr%type,
           v_hiredate emp_leave_record.hiredate%type,
           v_sal emp_leave_record.sal%type,
           v_comm emp_leave_record.comm%type,
           v_deptno emp_leave_record.deptno%type
    )
    as
    begin        
           insert into emp_leave_record (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno);
    end;
    
    --创建触发器
    create or replace trigger tri_pro   
    before delete 
    on scott.empc
    for each row
    begin   
       pro_leave_record(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
    end;
    
    delete empc where empno=7788;
    commit;
    
    delete empc where deptno=30;
    commit;
    
    select * from emp_leave_record;
    select * from empc;
    
    drop trigger tri_pro; 
    drop procedure pro_leave_record;
    drop table emp_leave_record;
    drop table empc;

## 例(七) ##

使用触发器实现序号自增长

--使用触发器实现序号自增长
    --创建一个测试表：
    create table tb_user(
      id number(11) primary key,
      username varchar(50),
      password varchar(250)
    );
    --创建一个序列：
    create sequence seq_user increment by 1 start with 1;
    --创建一个触发器：  
    create or replace trigger tri_seq_user
    before insert
    on tb_user
    for each row
    declare
        next_id number;
    begin     
        select seq_user.nextval into next_id from dual;
        :new.id := next_id; --:new 表示新插入的那条记录  把next_id赋值给新插入的那条记录的id
    end;
    
    --向表插入数据:
    insert into tb_user(username,password) values('张三','123');
    insert into tb_user(username,password) values('admin','hgr');
    commit;
     
    select * from tb_user;
    
    drop trigger tri_seq_user; 
    drop sequence seq_user; 
    drop table tb_user;

## 例(八) ##

创建触发器，利用视图插入数据

--insert of
    --创建触发器，利用视图插入数据
    --创建表
    create table tb_user (
           u_id number(4) primary key,
           u_name varchar2(40) not null,
           u_sex char(2),
           u_age number(2)
    );
    create table user_info(
           u_id number(4) primary key,
           u_tel varchar2(40) not null,
           u_card varchar2(20)
    );
    --插入数据
    insert into tb_user (u_id,u_name,u_sex,u_age) values (1001,'张三','男',23);
    insert into tb_user (u_id,u_name,u_sex,u_age) values (1002,'李四','男',35);
    insert into tb_user (u_id,u_name,u_sex,u_age) values (1003,'王五','女',27);
    commit;
    insert into user_info (u_id,u_tel,u_card) values(1001,'15911122234','500222111105223344');
    insert into user_info (u_id,u_tel,u_card) values(1002,'13466633367','500222112343677654');
    insert into user_info (u_id,u_tel,u_card) values(1003,'15823457643','500222786743343544');
    commit;
    --创建视图连接两张表
    
    create or replace force view view_user
    as
    select u.u_id,u.u_name,u.u_sex,u.u_age,ui.u_tel,ui.u_card from tb_user u,user_info ui where u.u_id=ui.u_id;
    --创建触发器
    create or replace trigger tri_user
     instead of insert
     on view_user
    begin
     insert into tb_user (u_id,u_name,u_sex,u_age) values (:new.u_id,:new.u_name,:new.u_sex,:new.u_age);
     insert into user_info (u_id,u_tel,u_card) values(:new.u_id,:new.u_tel,:new.u_card);
    end;
    
    --利用视图插入数据
    insert into view_user (u_id,u_name,u_sex,u_age,u_tel,u_card) values (1006,'admin','男',23,'1345228767','500222109497863245');
    commit;
    
    select * from view_user;
    select * from tb_user;
    select * from user_info;
    
    drop trigger tri_user;
    drop view view_user;
    drop table tb_user;
    drop table user_info;

## 例(九) ##

创建登录、退出触发器

--DDL事件   
      
    
    --创建登录、退出触发器。
     
    create table log_login(
     id number(5)primary key,
     username varchar2(10),
     address varchar2(20), 
     login_date timestamp,
     exit_date timestamp
    ); 
    
    create sequence seq_login increment by 1 start with 1;
    --创建登录触发器
    create or replace trigger tri_login
    after logon  
    on database
    begin 
       insert into log_login (id,username, address, login_date) values (seq_login.nextval,ora_login_user, ora_client_ip_address, systimestamp);
    end tr_login;
    --创建退出触发器
    create or replace trigger tri_exit
    before logoff
    on database
    begin
       insert into log_event (id,user_name, address, exit_date) values (seq_login.nextval,ora_login_user, ora_client_ip_address, systimestamp);
    end tri_exit;
        
    select * from log_login;
    
    drop trigger tri_login;
    drop trigger tri_exit;
    drop sequence seq_login;
    drop table log_login;

## 例(十) ##

创建触发器，将操作CREATE、DROP记录在log\_info表

--创建触发器，将操作CREATE、DROP记录在log_info表
    --创建表
    create table log_info(
      l_user varchar2(50),
      l_date varchar2(50),
      l_name varchar2(50),
      l_username   varchar2(50),
      l_type   varchar2(50)
    );
    
    --创建触发器
    create or replace trigger tri_log
     after create or drop 
     on schema
    begin
     insert into log_info
     values
      (user,sysdate,
       sys.dictionary_obj_name,
       sys.dictionary_obj_owner,
       sys.dictionary_obj_type);
    end;
    
    create table tb_test(id number);
    drop table tb_test;
    
    select * from log_info;
    
    drop trigger tri_log;
    drop table log_info;