oracle触发器的使用

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: 标识符过长。

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