Oracle中的触发器

迷南。 2024-03-22 16:46 12阅读 0赞

**目录**

一、什么是触发器？

二、触发器的基本原理

三、触发器的类型

3.1DML触发器：

3.1.1. 行级触发器（Row-Level Triggers）：

3.1.2. 语句级触发器（Statement-Level Triggers）：

3.2. 触发器的BEFORE类型和AFTER类型：

3.3. INSTEAD OF触发器：

3.4系统触发器（数据库触发器）：

四、触发器的作用

五、触发器使用场景

六、触发器的语法格式

七、触发器发生的相关概念

八、触发器和异常的联动应用

九、触发器的删除

十、触发器的两种状态

10.1. 启用状态：

10.2. 禁用状态：

十一、触发器信息获取及理解

十二、触发器的危害

--------------------

## 一、什么是触发器？ ##

触发器：**trigger**，是一种特殊的数据库对象，它可以在特定的事件发生时自动执行一些操作，可以用于实现复杂的数据约束、数据验证、数据审计等功能。

## 二、触发器的基本原理 ##

触发器类似于过程、函数，其包括声明部分、异常处理部分，并且都有名称、都被存储在数据库中。但与普通的过程、函数不同的是，函数需要用户显式地调用才执行，而触发器则是当某些事件发生时，由Oracle自动执行，触发器的执行对用户来说是透明的。对其总结有如下几点：

**2.1.触发器与表相关联：**

每个触发器都与一个特定的表相关联，并且只有在该表上发生的特定事件（如INSERT、UPDATE、DELETE）时才会触发。

**2.2.触发器定义：**

触发器定义包括触发事件、触发时间和触发操作（例如，执行一个存储过程或更新一个表）。

**2.3.触发器存储在数据库中：**

触发器是存储在数据库中的对象，它们与其他数据库对象一样可以被管理和维护。

**2.4.触发器是自动执行的：**

当与其相关联的表上发生特定事件时，触发器会自动执行，而不需要手动干预。

**2.5.触发器可以在事务中使用：**

触发器可以在事务中使用，以确保数据的一致性和完整性。

## 三、触发器的类型 ##

Oracle中的触发器类型有如下四种：

### 3.1DML触发器： ###

对表或视图执行DML操作时触发。其中包括：

#### 3.1.1. 行级触发器（Row-Level Triggers）： ####

当对表中的一行数据进行INSERT、UPDATE或DELETE操作时，会触发行级触发器执行。行级触发器可以在每行数据发生变化时，对该行数据进行操作。

#### 3.1.2. 语句级触发器（Statement-Level Triggers）： ####

当对表进行INSERT、UPDATE或DELETE操作时，会触发语句级触发器执行。语句级触发器可以在整个语句执行之前或之后对数据进行操作。

### 3.2. 触发器的BEFORE类型和AFTER类型： ###

BEFORE类型的触发器在数据插入、更新或删除之前触发，可以用来验证数据的正确性、设置默认值等；AFTER类型的触发器在数据插入、更新或删除之后触发，可以用来记录数据变化、更新其他表等。

### 3.3. INSTEAD OF触发器： ###

INSTEAD OF触发器是一种特殊的触发器，它可以用来替代INSERT、UPDATE或DELETE操作，当对视图进行INSERT、UPDATE或DELETE操作时，可以通过INSTEAD OF触发器对其进行操作。它只定义在视图上，用来替换实际的操作语句。

### 3.4系统触发器（数据库触发器）： ###

每当一个用户或数据库中一个数据事件发生时或系统事件（如登录或关闭系统）发生时触发，即对数据库系统进行操作（如DDL语句、启动或关闭数据库等系统事件)时触发。

在Oracle11g和Oracle12c中可以定义和使用的触发器类型总结有以下几种：

*  Ⅰ、简单DML触发器：包括BEFORE、AFTER和INSERT OF触发器；
 *  Ⅱ、组合（复合）触发器；
 *  Ⅲ、非DML触发器，包括DDL事件触发器和数据库事件触发器。

## 四、触发器的作用 ##

主要作用包括：

**4.1. 数据完整性控制：**

触发器可以用于控制数据库中数据的完整性，例如在插入、更新或删除数据时验证数据的有效性，确保数据满足特定的业务规则和约束条件。

**4.2. 数据库自动化：**

触发器可以自动化一些常见的数据库操作，例如在插入数据时自动更新其他表的数据、在删除数据时自动删除相关的数据等。

**4.3. 数据审计：**

触发器可以用于跟踪数据库中的数据变化，例如记录数据的修改时间、修改人、修改前后的值等，以及在发生异常情况时自动发送警报。

**4.4. 数据复制：**

触发器可以用于实现数据库的数据复制，例如将一个数据库中的数据自动复制到另一个数据库中，以实现数据同步和备份。

**4.5. 业务流程自动化：**

触发器可以用于自动化业务流程，例如在订单提交后自动发送邮件通知客户、在库存不足时自动向供应商下订单等。

## 五、触发器使用场景 ##

在以下情况下可以使用触发器：

*  Ⅰ、对一个特定的操作要确保所有相关的操作都被执行；
 *  Ⅱ、执行集中的全局的操作，并且触发器的语句的触发器独立于用户，也独立于发出语句的应用程序。

在以下情况不必设计触发器：

*  Ⅰ、其功能已经嵌入Oracle服务器，如实现完整性规则应该声明Oracle约束，而不是定义触发器；
 *  Ⅱ、重复其他触发器的功能。

## 六、触发器的语法格式 ##

创建触发器的语句是**CREATE TRIGGER**，其语法格式如下：

--整体组成部分包括：
    CREATE OR REPLACE TRIGGER<触发器名>
    触发条件
    触发体
    ---------------------------------------------------
    CREATE [OR REPLACE] TRIGGER trigger_name
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
    ON table_name [FOR EACH ROW]
    [WHEN (condition)]
    DECLARE
    -- declarations
    BEGIN
    -- trigger body
    END;
    
    --理解如下：
    create or replace trigger tri_name
    before|after                                         --before是事前触发器,after是事后触发器
    dml操作 [of 列] on 表 |   dml操作 or  dml操作 or....   --insert on update on delete   
    如果是插入则..删除则..更新则..
    前者锁定某个操作针对表，后者针对不同的dml操作
    [for each row]--默认语句级,写上是行级
    [declare 声明]
    begin
      要执行的语句;
    end;

举例：

创建emp1和emp2表，其中emp1有emp的20部门的数据,emp2是空表,表格式同emp；

create table emp1 as select * from emp where deptno=20;
    create table emp2 as select * from emp where 1=2;

此时emp1表的数据如下：

![f4fd1ef410f748139ff677b5af408cc4.png][] emp2表如下：

![c9c91a33455a4bd3b814cbef0cc09235.png][]

对emp1创建触发器，要求是当它被插入数据时,向emp2的empno插入一个1；

create or replace trigger tri_insert before
    insert on emp1
    for each row
    begin
      insert into emp2(empno)  values (1);
      --commit;
    end;

测试： 假如向emp1表插入10部门的员工信息：

insert into emp1 select * from emp where deptno=10;

执行后，看emp1表：

![d89f1711f40d4464822c742ad68bb332.png][]

查看emp2表：

![28507cf55b004ebe95816d4a423a4d19.png][]

发现表中插入了三个1，即emp1表每插入一条数据，emp2表就插入一个1。

那么再创建一个触发器，要求是当emp1表被删除数据时，向emp2表的ename列插入" delete" 向hiredate列插入"删除时间",则语句如下：

create or replace trigger t1 before
    delete on emp1
    for each row
    begin
     insert into emp2(ename,hiredate) values('delete',sysdate);
    end;

测试：假如删除emp1表20部门的员工信息 ：

delete from emp1 where deptno=20；

执行后，查看emp1表：

![f13e61be58f447ecbab9c64029a686dc.png][]

查看emp2表：

![f1184061ed8e44d387d8dcc85795702c.png][]

此时发现emp2中已经多了5条数据。

## 七、触发器发生的相关概念 ##

**（1）触发事件**

引起触发器被触发的事件。如DML语句（如INSERT、UPDATE、DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象)、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。

其中：

*  1. INSERT：当有新记录插入到表中时触发触发器。
 *  2. UPDATE：当表中的记录被修改时触发触发器。
 *  3. DELETE：当表中的记录被删除时触发触发器。

**（2）触发条件**

触发条件是由WHEN子句指定的一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作，否则即便遇到触发事件也不会执行触发器。

**（3）触发对象**

触发对象包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。

**（4）触发操作**

触发器所要执行的PL/SQL程序，即执行部分。

**（5）触发时机**

触发时机指定触发器的触发时间。如果指定为BEFORE,则表示在执行DML操作之前触发，以便防止某些错误操作发生或实现某些业务规则：如果指定为AFTER,则表示在DML操作之后触发，以便记录该操作或做某些事后处理  
**（6）条件谓词**

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

*  INSERTING：当触发事件是INSERT时，取值为TRUE,否则为FALSE。
 *  UPDATING\[(column\_1,column\_2,,column\_n)\]：当触发事件是UPDATE时，如果修改了column\_x列，则取值为TRUE,否则为FALSE,其中column\_x是可选的。
 *  DELETING：当触发事件是DELETE时，取值为TRUE,否则为FALSE。

**（7）触发子类型**  
触发子类型分别为行触发(row)和语句触发(statement)，行触发即对每一行操作时都要触发，而语句触发只对这种操作触发一次（即行级别触发器可以在每个受影响的行上执行操作，而语句级别触发器则只在整个语句执行完成后执行一次。）。一般进行SQL语句操作时都应是行触发，只有对整个表作安全检查（即防止非法操作）时才用语句触发。如果省略此项，默认为语句触发。

***对比语句级触发器和行级触发器***：

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td style="text-align:center;"><strong><span style="color:#fe2c24;">语句级触发器</span></strong></td> 
   <td style="text-align:center;"><strong><span style="color:#fe2c24;">行级触发器</span></strong></td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">是创建触发器时的默认类型</td> 
   <td style="text-align:center;">创建触发器时使用FOR EACH ROW子句</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">对于触发的事件只触发一次</td> 
   <td style="text-align:center;">对受触发事件影响的每行触发一次</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">没有受影响的行时也要触发一次</td> 
   <td style="text-align:center;">触发事件未影响任何数据行就不触发</td> 
  </tr> 
 </tbody> 
</table>

此外，触发器中还有两个相关值，分别对应被触发的行中的***旧表值和新表值***，用***old和new***来表示，可以在触发器中访问NEW和OLD伪记录。

:old.列--之前的,老的数据
    :new.列--之后的,新的数据

**注意：**

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td style="text-align:center;"></td> 
   <td style="text-align:center;">insert</td> 
   <td style="text-align:center;">update</td> 
   <td style="text-align:center;">delete</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">:old.列&nbsp;</td> 
   <td style="text-align:center;">null</td> 
   <td style="text-align:center;">修改之前的值</td> 
   <td style="text-align:center;">删除之前的值</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">:new.列</td> 
   <td style="text-align:center;">插入的值</td> 
   <td style="text-align:center;">修改之后的值</td> 
   <td style="text-align:center;">null</td> 
  </tr> 
 </tbody> 
</table>

> 在使用OLD和NEW限定词时，还需要注意：
> 
>  *  只在行触发器中有OLD和NEW限定词；
>  *  在每个SQL和PL/SQL语句中，这两个限定词前必须冠以冒号（：）；
>  *  如果这两个限定词是在WHEN所在条件中引用就不必冠以冒号；
>  *  如果在较大的表上执行许多修改，行级触发器可能降低系统的效率。

把上面例子中emp2表的hiredate列的类型改成timestamp；

alter table emp2 modify hiredate timestamp;

> **注意：**
> 
> 要修改列的类型时该列一定要为空null；

原来hiredate的类型是varchar，如图：

![3737eb6bf2da40d28ca253b8713aec6e.png][]

更改后如图：

![894c76dede9940419a2cf2aba28295e7.png][]

按照下面要求完成：

向emp1创建触发器，当emp1的ename被更新时，向emp2的ename插入更新前和更新后的名字，job列插入更新前/更新后，hiredate插入更新时的系统时间戳。则语句如下：

create or replace trigger tri_update before
    update of ename on emp1
    for each row
    begin
      insert into emp2(ename,job,hiredate) 
        values(:old.ename,'更新前',systimestamp);
      insert into emp2(ename,job,hiredate)
        values(:new.ename,'更新后',systimestamp);
    --commit;
    end;

测试：假如将emp1表的ename列改为小写：

update emp1 set ename=lower(ename);

那么执行后，查看emp1表：

![fc44f705d5104380bbdc2a3b73776d67.png][]

查看emp2表：

![5ac3d9589cd74421bc505dfde41f0fdc.png][]

通过表结果可以看出emp1更新三条记录，emp2就会插入对应的3条更新前/后的数据记录。

## 八、触发器和异常的联动应用 ##

比如：

向emp1表插入数据时的当前时间小时数为12和24之间时，抛出异常，错误代码为-20000，错误描述为“时间不符”，那么语句如下：

create or replace trigger tri_1 before
    insert on emp1
    begin
      if to_char(sysdate,'hh24') between 12 and 24 then
      raise_application_error(-20000,'时间不符');
      end if;
    end;

测试：假如此时向emp1表插入emp表的全部数据 ：

--查看当前时间：
    select sysdate from dual;

结果如下：

![c7074efe618845a3a5e772e3def6bbdc.png][]

开始插入数据：

insert into emp1 select * from emp;

执行后，页面出现弹窗如图所示：

![6e5a676dd68d43ddb46ee2c6010f6a8f.png][]

*使用触发器也能实现一表多插的情况 ：*

比如：对emp1创建一个触发器,当向emp1插入数据时,  
\--如果部门是10部门,同时插入到emp2表  
\--如果部门是20部门,打印部门编号和姓名  
\--如果部门是30部门,插入后工资增加10000；

create or replace trigger tri_2 before
    insert on emp1
    for each row
    begin
      if :new.deptno= 10 then
        insert into emp2 values(:new.empno, :new.ename, 
                             :new.job, :new.mgr, :new.hiredate, 
                             :new.sal, :new.comm, :new.deptno);
      elsif :new.deptno=20 then
        dbms_output.put_line(:new.deptno||' '||:new.ename);
      elsif :new.deptno=30 then
        :new.sal:=:new.sal+10000;
      end if;
    end;

测试：表emp1和emp2是空表，假如向emp1插入emp表的数据：

insert into emp1 select * from emp;

那么执行后查看表emp1，如图所示：

![17fa9cd15e5c4216b12e76a0a5b77df3.png][]

为了方便对比，查询下emp表30部门的薪资：

select *  from emp where deptno=30;

![7f833b0865bf4bc884f35d98f40cc822.png][]

查看emp2如图所示：

![fbb57ee4e2ec4cb5949c37e7fdc1db77.png][]

从表中可以看出10部门数据已经插入到emp2表中。

## 九、触发器的删除 ##

触发器可以通过**DROP TRIGGER**语句删除，语法如下：

DROP TRIGGER trigger_name;

## 十、触发器的两种状态 ##

在Oracle中，触发器有两种状态：**启用状态和禁用状态**。

### 10.1. 启用状态： ###

当触发器处于启用状态时，它会自动执行预定义的操作，以响应与其相关联的事件。可以使用***ALTER TRIGGER***语句来启用触发器，例如：

ALTER TRIGGER trigger_name ENABLE;

### 10.2. 禁用状态： ###

当触发器处于禁用状态时，它不会响应与其相关联的事件，也不会自动执行预定义的操作。可以使用***ALTER TRIGGER***语句来禁用触发器，例如：

ALTER TRIGGER trigger_name DISABLE;

可以使用以下语句来查看触发器的状态：

SELECT trigger_name, status FROM user_triggers;

触发器的启用状态和禁用状态可以根据具体的业务需求来灵活切换。例如，在开发和测试阶段，可以禁用某些触发器来方便调试和测试，而在生产环境中，应该保证触发器处于启用状态，以确保数据的完整性和一致性。

为什么要引入两种状态？

Oracle引入触发器的两种状态（激活状态和禁用状态）是为了方便管理和控制触发器的执行。触发器的激活状态表示触发器可以被自动触发执行，而禁用状态表示触发器不会被自动触发执行。通过将触发器设置为禁用状态，可以临时停止触发器的执行，以便进行维护或调试。同时，禁用状态还可以用来控制触发器的执行顺序，以便在需要的时候先执行某些触发器。触发器的状态可以通过ALTER TRIGGER语句来进行修改。

## 十一、触发器信息获取及理解 ##

已经会创建和使用触发器，那么应该如何获取当前用户中有关触发器的信息以及如何理解这些信息？

其实可以**使用Oracle系统视图**来获取当前用户中有关触发器的信息，其中包括以下几个常用视图：

*  1. ALL\_TRIGGERS：显示当前用户下的所有触发器信息，包括触发器名称、所属表名、触发器类型、触发事件、触发器状态等。
 *  2. USER\_TRIGGERS：与ALL\_TRIGGERS类似，但只显示当前用户下的触发器信息。
 *  3. ALL\_TRIGGER\_COLS：显示当前用户下所有触发器相关的列信息，包括列名、数据类型、长度等。
 *  4. USER\_TRIGGER\_COLS：与ALL\_TRIGGER\_COLS类似，但只显示当前用户下的触发器相关的列信息。

这些视图提供了触发器的相关信息，可以通过查询这些视图来获取需要的信息。例如，可以使用以下SQL语句查询当前用户下的所有触发器信息：

SELECT trigger_name, table_name, trigger_type, triggering_event, status
    FROM user_triggers;

其中，trigger\_name表示触发器名称，table\_name表示触发器所属表名，trigger\_type表示触发器类型（BEFORE或AFTER），triggering\_event表示触发事件（INSERT、UPDATE或DELETE），status表示触发器状态（ENABLED或DISABLED）。

理解这些信息可以帮助我们更好地管理和控制触发器的执行，例如，通过查看触发器状态来判断是否需要禁用该触发器，或者通过查看触发器相关的列信息来确定触发器的执行逻辑。

## 十二、触发器的危害 ##

**12.1.性能问题：**

如果触发器逻辑复杂，执行时间长，会影响整个数据库的性能，尤其是在高并发环境下。

**12.2.数据不一致：**

如果触发器逻辑有误或触发器与其他数据库对象之间的关系不正确，可能会导致数据不一致的问题。

**12.3.难以维护：**

触发器可以在多个表之间进行数据同步或其他操作，但触发器的复杂性和维护难度也会随之增加。

**12.4.安全问题：**

如果触发器没有正确限制访问权限，可能会被恶意用户利用，造成安全漏洞。

> **总结：**
> 
> 1.  触发器的执行会增加数据库的负载，从而影响数据库的性能。
> 2.  触发器的设计和维护需要较高的技能水平和经验，否则可能会引起数据不一致等问题。
> 3.  触发器的执行顺序可能会影响业务逻辑，需要进行合理的设计和管理。
> 4.  触发器的使用可能会增加数据库的复杂性，从而增加了数据库管理的难度。

> ***遗留问题（待解决）***：
> 
> 1.  怎样利用触发器来实现完整性约束？
> 2.  INSTEAD OF触发器的创建和测试如何实现？
> 
> 目前还未理解这两个问题，后续会花时间再学习和总结一下！！！
> 
> 熬夜肝了那么久梳理了一份触发器的笔记，希望对在学习的朋友有帮助，感谢你们的查看，点个关注和爱心吧！！

[f4fd1ef410f748139ff677b5af408cc4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/e02fed9975794d65bd3df37a1d5f4e5c.png
[c9c91a33455a4bd3b814cbef0cc09235.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/385fcf76a6bb485b9122bfda851d14c6.png
[d89f1711f40d4464822c742ad68bb332.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/a0d8fb00cb7a457496850a2758b58148.png
[28507cf55b004ebe95816d4a423a4d19.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/01e07635a5984252961e47b679e5a9b5.png
[f13e61be58f447ecbab9c64029a686dc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/fde7bb2a76744a32b1c4fe3d4b99d697.png
[f1184061ed8e44d387d8dcc85795702c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/ee4f706aa0ca4d12bf5a61aee1ae5fff.png
[3737eb6bf2da40d28ca253b8713aec6e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/4233e1e28dae47b6babe5ed3e2be4d23.png
[894c76dede9940419a2cf2aba28295e7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/cf4d37b278ea43eba651db160b2d903e.png
[fc44f705d5104380bbdc2a3b73776d67.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/6b92470ba19e4c50910fab1c8241d360.png
[5ac3d9589cd74421bc505dfde41f0fdc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/c50cceda5b2d49eb8ba88822135e3a6a.png
[c7074efe618845a3a5e772e3def6bbdc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/82234ba1141b4b1da7f024d6a2d0933f.png
[6e5a676dd68d43ddb46ee2c6010f6a8f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/f1d0b2db56da46b189bda391a5df1642.png
[17fa9cd15e5c4216b12e76a0a5b77df3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/0c104ed8b9db4f23ac44030eafa4c607.png
[7f833b0865bf4bc884f35d98f40cc822.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/d3f9b783bc3d41288f3c9c68144fe2d0.png
[fbb57ee4e2ec4cb5949c37e7fdc1db77.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/54496f32dabb42d494381512b7af8a1b.png