Oracle笔记（十）：触发器

清疚 2022-03-24 14:48 275阅读 0赞

**目录**

一、概述

二、创建单个执行语句的触发器

实例1：一个超简单的单执行语句

实例2：每当插入或更新表时自动给某字段赋值。

案例3：old值的使用

三、创建多个执行语句的触发器

实例：一个触发器，多个执行语句，操作多个表。

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

## 一、概述 ##

Oracle的触发器（Trigger）与存储过程一样，都是嵌入到Oracle的一段程序。它是由事件触发某个操作，包括INSERT, UPDATE, DELETE语句。当数据库执行上述DML，就会激发触发器执行相应操作。触发程序是与表有关的命名数据库对象。

Trigger是特殊的存储过程，但不需要手动来调用（如：EXEC语句）。

## 二、创建单个执行语句的触发器 ##

**公式：**

CREATE TRIGGER trigger_name  trigger_time  trigger_event
        ON tb_name
       FOR EACH ROW trigger_stmt

*  trigger\_time：触发器时机( BEFORE.../ AFTER... )
 *  trigger\_event：标识触发事件( INSERT, UPDATE, DELETE )
 *  trigger\_stmt：触发器程序体( BEGIN...END )

### **实例1：一个超简单的单执行语句** ###

**首先创建一个account表 **

CREATE TABLE tb_account 
    (
      acct_num NUMBER(6), amount NUMBER(10,2)
    );

**SQL> desc  tb\_account;  
Name     Type         Nullable Default Comments   
\-------- ------------ -------- ------- --------   
ACCT\_NUM NUMBER(6)    Y                           
AMOUNT   NUMBER(10,2) Y              **

**创建触发器 INS\_SUM**

create or replace trigger INS_SUM
      before insert
      on tb_account 
    BEGIN
             DBMS_OUTPUT.PUT_LINE('下面将开始插入数据');
    END INS_SUM;

**插入后结果为：**

**SQL> INSERT INTO tb\_account values(1,8900.23);**

**下面将开始插入数据  
1 row inserted**

### **实例2：每当插入或更新表时自动给某字段赋值。** ###

假设有一个员工表：

**SQL> DESC employees  
Name             Type         Nullable Default Comments   
\---------------- ------------ -------- ------- --------   
EMPLOYEE\_ID      NUMBER(6)    Y                           
FIRST\_NAME       VARCHAR2(20) Y                           
LAST\_NAME        VARCHAR2(25) Y                           
SALARY           NUMBER(8,2)  Y                           
LAST\_UPDATE\_TIME TIMESTAMP(6) Y     **

**创建触发器 emp\_last\_update\_time**

CREATE OR REPLACE TRIGGER  emp_last_update_time
    BEFORE INSERT OR UPDATE 
        ON employees
    REFERENCING NEW AS nnn        -- 做了个引用，取别名
    FOR EACH ROW
    BEGIN
    	:nnn.last_update_time := current_timestamp;
    END;
    /
    
    INSERT INTO employees VALUES (1, 'Scb', 'Leonardo', 8888, null);

**SQL> SELECT \* FROM EMPLOYEES;**

**EMPLOYEE\_ID FIRST\_NAME           LAST\_NAME                     SALARY LAST\_UPDATE\_TIME  
\----------- -------------------- ------------------------- ---------- --------------------------------------------------------------------------------  
          1 Scb                   Leonardo                                  8888.00 22-1月 -19 03.44.51.234000 下午**

### 案例3：old值的使用  ###

** employees 表的 salary 值是非常重要的，  
  对它的任何修改我们都希望记录下来 **

**  创建相同表结构的employees\_hist**

CREATE table employees_hist 
        AS SELECT * FROM employees 
     WHERE 1=0;

**创建触发器emp\_salary**

CREATE OR REPLACE TRIGGER  emp_salary  
    BEFORE UPDATE OF salary 
        ON employees  
    REFERENCING old AS o  
    FOR EACH ROW  
    BEGIN
        INSERT INTO employees_hist VALUES (:o.employee_id, :o.first_name,
                                           :o.last_name, :o.salary, current_timestamp); 
    END;
    /

UPDATE薪酬记录

UPDATE EMPLOYEES SET salary=6699.00 WHERE LAST_NAME='Leonardo';

**SQL> SELECT \* FROM employees;**

**SQL> SELECT \* FROM employees\_hist;**

**EMPLOYEE\_ID FIRST\_NAME           LAST\_NAME                     SALARY LAST\_UPDATE\_TIME  
\----------- -------------------- ------------------------- ---------- --------------------------------------------------------------------------------  
          1 Scb                   Leonardo                                  ****8888.00** **22-1月 -19 04.18.16.186000 下午**

**before 和after的区别:**

*  before：insert update 可以对new进行修改。
 *  after :不能对new 进行修改
 *  二者都不能对old 进行修改

## 三、创建多个执行语句的触发器 ##

### **实例：一个触发器，多个执行语句，操作多个表。** ###

**首先创建4张表**

CREATE TABLE test1(a1 NUMBER(6));
    CREATE TABLE test2(a2 NUMBER(6));
    CREATE TABLE test3(a3 NUMBER(6) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY);
    CREATE TABLE test4(
      a4 NUMBER(6) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,   -- Oracle12c版本以上适用
      b4 NUMBER(6) DEFAULT 0
    );

** 创建触发器：**

CREATE TRIGGER TESTREF 
    BEFORE INSERT 
        ON test1
       FOR EACH ROW
     BEGIN
        INSERT INTO test2 SET a2 = NEW.a1;    -- Oracle12c写法
        DELETE FROM test3 WHERE a3 = NEW.a1;  
        UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
     END;

CREATE OR REPLACE TRIGGER TESTREF
    BEFORE INSERT
        ON test1
       FOR EACH ROW
     BEGIN
        INSERT INTO test2 VALUES (:NEW.a1);            --11g写法
        DELETE FROM test3 WHERE a3 = :NEW.a1;
        UPDATE test4 SET b4 = b4 + 1 WHERE a4 = :NEW.a1;
     END;

** 向test1表中插入多条记录：**

**SQL> INSERT INTO test1 VALUES (1);  
SQL> INSERT INTO test1 VALUES (3);  
SQL> INSERT INTO test1 VALUES (1);  
SQL> INSERT INTO test1 VALUES (7);  
SQL> INSERT INTO test1 VALUES (7);  
SQL> INSERT INTO test1 VALUES (8);  
SQL> INSERT INTO test1 VALUES (4);  
SQL> INSERT INTO test1 VALUES (4); **

**那么4个表中的数据如下：**  
**SQL> SELECT \* FROM test1;  
 A1     
\------  
  1   
  3   
  1  
  7   
  1   
  8  
  4  
  4**

**--------  
SQL> SELECT \* FROM test2;  
A2     
\------  
 1   
 3   
 1  
 7   
 1   
 8  
 4   
 4 **

**SQL> SELECT \* FROM test3;  
 A3  
\----  
 2  
 5   
 6  
 9  
 10 **

**   
SQL> SELECT \* FROM test4;**

** A4  B4     
\----  ------  
  1   3  
  2   0   
  3   1   
  4   2  
  5   0  
  6   0  
  7   1   
  8   1   
  9   0   
 10   0 **

更多参考：[https://blog.csdn.net/shangboerds/article/details/43030185][https_blog.csdn.net_shangboerds_article_details_43030185]

### **实例：INSTEAD OF触发器操作视图** ###

**对于简单视图，可以直接执行INSERT，UPDATE和DELETE操作**  
**但是对于复杂视图，不允许直接执行INSERT，UPDATE和DELETE操作。**  
**为了在具有以上情况的复杂视图上执行DML操作需要使用 INSTEAD OF触发器来完成。**

**创建视图 order\_info  **

CREATE OR REPLACE VIEW order_info AS
    SELECT c.customer_id, c.cust_last_name, 
           c.cust_first_name, o.order_id, o.order_date, o.order_status
      FROM customers c, orders o
     WHERE c.customer_id = o.customer_id;

用户信息&对应订单信息集合的视图 。

**定义INSTEAD OF触发器**

CREATE OR REPLACE TRIGGER order_info_insert
    INSTEAD OF INSERT ON order_info
    BEGIN
    	INSERT INTO customers (customer_id, cust_last_name, cust_first_name)
            VALUES (:new.customer_id, :new.cust_last_name, :new.cust_first_name);	
    	INSERT INTO orders (order_id, order_date, customer_id) 
            VALUES (:new.order_id,  :new.order_date, :new.customer_id);
    END order_info_insert;

若order\_info视图被插入，就进行多个表的记录插入

[https_blog.csdn.net_shangboerds_article_details_43030185]: https://blog.csdn.net/shangboerds/article/details/43030185