oracle触发器的使用

绝地灬酷狼 2022-11-05 00:57 222阅读 0赞

### 文章目录 ###

*   *  基本操作
     *  创建触发器
     *   *  Example 9-1 Trigger Uses Conditional Predicates to Detect Triggering Statement(触发器使用条件谓词来检测触发语句)
         *  Example 9-13 Trigger Derives New Column Values(触发器派生新列值)
         *  Example 9-2 INSTEAD OF Trigger
         *  
         *  Example 12-10 FORALL Statement for Subset of Collection(集合的子集)
         *  Bulk-Selecting into Nested Table of Records(批量选择嵌套记录表)
     *  其他
     *   *  官网文档
         *  :new 和 :old
         *   *  NEW关键字在什么情况下使用？
         *  冒号\`:\` 是什么意思
         *  DBMS\_OUTPUT.PUT\_LINE('记录已经成功更新，并已记录到日志');
         *  如何抛出异常
         *  触发器名称的长度限制

oracle中的触发器博大精深，要吃透非一日可行。  
通过学习，对触发器有个基本的认识，能够写出简单的触发器。 也就初步达到了目的。

触发器有几种类型。  
这里只看下最常用的dml触发器。

## 基本操作 ##

语句：

查看所有触发器：
    select trigger_name from all_triggers
    
    根据表名查看触发器(注：表名要大写，基本常识)：
    select trigger_name from all_triggers where table_name='TESTTABLE'; 
    
    根据触发器查询信息(注：这里的type和name要大写,基本常识)：
    select text from all_source where type='TRIGGER' AND name='TEST_TRI';
    
    删除触发器：
    DROP TRIGGER TEST_TRI;

## 创建触发器 ##

基础语法(不全)：

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
    ON table_name
    [
     -- 触发器逻辑....
    ];

触发器的一个例子：

-- 实际的触发器
    CREATE OR REPLACE TRIGGER TEST_TRI
     AFTER DELETE OR INSERT OR UPDATE ON TESTTABLE
    DECLARE
    V_TYPE TEST_LOG.L_TYPE%TYPE;
    BEGIN
     IF INSERTING THEN
      --INSERT触发
      V_TYPE := 'INSERT';
      DBMS_OUTPUT.PUT_LINE('记录已经成功插入，并已记录到日志');
     ELSIF UPDATING THEN
      --UPDATE触发
      V_TYPE := 'UPDATE';
      DBMS_OUTPUT.PUT_LINE('记录已经成功更新，并已记录到日志');
     ELSIF DELETING THEN
      --DELETE触发
      V_TYPE := 'DELETE';
      DBMS_OUTPUT.PUT_LINE('记录已经成功删除，并已记录到日志');
     END IF;
     INSERT INTO TEST_LOG
     VALUES
      (USER, V_TYPE, TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss')); --USER表示当前用户名
    END;
    /

### Example 9-1 Trigger Uses Conditional Predicates to Detect Triggering Statement(触发器使用条件谓词来检测触发语句) ###

官网例子9-1。 展示根据动作类型来触发语句。

CREATE OR REPLACE TRIGGER t
      BEFORE
        INSERT OR
        UPDATE OF salary, department_id OR  
        DELETE
      ON employees
    BEGIN
      CASE
        WHEN INSERTING THEN
          DBMS_OUTPUT.PUT_LINE('Inserting');
        WHEN UPDATING('salary') THEN
          DBMS_OUTPUT.PUT_LINE('Updating salary');
        WHEN UPDATING('department_id') THEN
          DBMS_OUTPUT.PUT_LINE('Updating department ID');
        WHEN DELETING THEN
          DBMS_OUTPUT.PUT_LINE('Deleting');
      END CASE;
    END;
    /

### Example 9-13 Trigger Derives New Column Values(触发器派生新列值) ###

CREATE OR REPLACE TRIGGER Derived 
    BEFORE INSERT OR UPDATE OF Ename ON Emp
    /* Before updating the ENAME field, derive the values for the UPPERNAME and SOUNDEXNAME fields. Restrict users from updating these fields directly: */
    FOR EACH ROW
    BEGIN
      :NEW.Uppername := UPPER(:NEW.Ename);
      :NEW.Soundexname := SOUNDEX(:NEW.Ename);
    END;
    /

### Example 9-2 INSTEAD OF Trigger ###

官网例子9-2。  
视图本身是不能更新的，因为他的索引不唯一。  
这里用的方法是更新索引来源表的相关数据。

1.  基于view表单的处理可以在表和视图上指定一个instead of 触发器
2.  执行这种触发器可以代替原来的触发器,instead of 触发器扩展了视图跟新类型
3.  每一个表和视图只能有一个instead of 触发器
4.  INSTEAD OF触发器被用于更新那些没有办法通过正常方式更新的视图
5.  INSTEAD OF触发器的主要优点就是可以使不能更新的视图支持更新。基于多个表的视图必须使用。
6.  INSTEAD OF触发器来支持多个表中数据的插入、更新和删除操作。  
    注：不能在带有with check option 定义的视图中创建INSTEAD OF触发器

语句：

-- 这是准备语句
    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;
    
    -- 触发器
    CREATE OR REPLACE TRIGGER order_info_insert
       INSTEAD OF INSERT ON order_info
       DECLARE
         duplicate_info EXCEPTION;
         PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
       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);
       EXCEPTION
         WHEN duplicate_info THEN
           RAISE_APPLICATION_ERROR (
             num=> -20107,
             msg=> 'Duplicate customer or order ID');
       END order_info_insert;
    /

###  ###

### Example 12-10 FORALL Statement for Subset of Collection(集合的子集) ###

-- 准备
    DROP TABLE employees_temp;
    CREATE TABLE employees_temp AS SELECT * FROM employees;
    
    -- 声明
    DECLARE
      TYPE NumList IS VARRAY(10) OF NUMBER;
      depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);
    BEGIN
      FORALL j IN 4..7
        DELETE FROM employees_temp WHERE department_id = depts(j);
    END;
    /

### Bulk-Selecting into Nested Table of Records(批量选择嵌套记录表) ###

DECLARE
      CURSOR c1 IS
        SELECT first_name, last_name, hire_date
        FROM employees;
      
      TYPE NameSet IS TABLE OF c1%ROWTYPE;
     
      stock_managers  NameSet;  -- nested table of records
     
    BEGIN 
      -- Assign values to nested table of records:
     
      SELECT first_name, last_name, hire_date
        BULK COLLECT INTO stock_managers
        FROM employees
        WHERE job_id = 'ST_MAN'
        ORDER BY hire_date;
     
      -- Print nested table of records:
     
        FOR i IN stock_managers.FIRST .. stock_managers.LAST LOOP
          DBMS_OUTPUT.PUT_LINE (
            stock_managers(i).hire_date || ' ' ||
            stock_managers(i).last_name  || ', ' ||
            stock_managers(i).first_name
          );
        END LOOP;END;
    /

## 其他 ##

### 官网文档 ###

触发器文档地址：  
`https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-triggers.html#GUID-217E8B13-29EF-45F3-8D0F-2384F9F1D231`

或者从帮助中心，输入trigger，然后搜索，也一样可以找到文档。

### :new 和 :old ###

触发器中经常会用到NEW和OLD两个关键字，这是系统默认的吗？只要带上NEW或者OLD，系统就知道是新数据还是老数据。

#### NEW关键字在什么情况下使用？ ####

oracle默认的 用old代表老数据 new代表新数据 不过二者在使用时是有限制的  
insert时 只有new 没有old  
delete时 只有old 没有new  
update时 二者都可用

这两个变量只有在使用了关键字 "FOR EACH ROW"时才存在.且update语句两个都有,而insert只有:new ,delect 只有:old;

### 冒号`:` 是什么意思 ###

表示引用变量。  
可以是自定义的变量：

定义：
    DECLARE
      name_list ora_name_list_t;
      
    引用：
    :name_list

也可以是自带的变量：

:new  -- new是系统自带的变量

### DBMS\_OUTPUT.PUT\_LINE(‘记录已经成功更新，并已记录到日志’); ###

### 如何抛出异常 ###

EXCEPTION
      WHEN OTHERS THEN
        INSERT INTO emp_log (Emp_id, Log_date, New_salary, Action)
          VALUES (99, SYSDATE, NULL, 'Could not insert row.');
    
      RAISE_APPLICATION_ERROR (-20000, 'Remote database is unavailable.');

### 触发器名称的长度限制 ###

要求在30个字符以内，超过会报错，提示：ORA-00972: 标识符过长。

其实这个不单是触发器的长度，表名等也有这样的限制。