从多个方面阐述 Oracle 触发器

一、概述

Oracle 触发器是一种特殊的存储过程，它可以自动地在指定的表上执行一些操作。通常情况下，Oracle 触发器是在一个表上进行某些操作时，自动地执行一些针对该表的逻辑，例如在插入、更新或删除某行数据时，自动更新其他关联的表。

Oracle 触发器通常与数据库中的日志记录和审计功能一起使用，可以极大地提高系统的数据完整性和安全性。

二、Oracle 触发器的种类

Oracle 触发器分为三种类型：BEFORE 触发器、AFTER 触发器和 INSTEAD OF 触发器。

1. BEFORE 触发器

BEFORE 触发器会在对表进行插入、更新或删除操作之前执行，可以用该触发器阻止对表进行不允许的操作，或者在操作之前进行一些额外的验证。

2. AFTER 触发器

AFTER 触发器会在对表进行插入、更新或删除操作之后执行，可以用该触发器响应表的更改，或者在操作之后执行一些额外的操作。

3. INSTEAD OF 触发器

INSTEAD OF 触发器会代替对表的插入、更新或删除操作执行，可以用该触发器修改或处理数据，或者完全更改表的操作方式。

三、触发器的语法

Oracle 触发器的语法如下：

CREATE [ OR REPLACE ] TRIGGER trigger_name [ BEFORE | AFTER | INSTEAD OF ] {event_type}
ON {table_name | view_name}
[ FOR EACH ROW ]
[ WHEN condition ]
[ DECLARE ]
declaration_section
BEGIN
executable_section
END;

其中，event_type 可以是 INSERT、UPDATE 或 DELETE，用于指定触发器执行的事件类型；WHEN condition 可以用于限制触发器执行的条件，不满足条件不执行触发器；FOR EACH ROW 表明 Oracle 将为更新的每行执行该触发器；DECLARE 和 BEGIN...END 用来声明和执行触发器中的变量和 SQL 语句。

四、应用示例

1. 在插入订单时，自动更新库存

CREATE OR REPLACE TRIGGER update_inventory
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE inventory
  SET quantity = quantity - :NEW.quantity
  WHERE product_id = :NEW.product_id;
END;

该触发器定义了在 orders 表中插入一行后，更新 inventory 表中与该订单关联的产品的库存数量。

2. 防止删除用户时，删除有关订单

CREATE OR REPLACE TRIGGER check_orders
BEFORE DELETE ON users
FOR EACH ROW
DECLARE
  user_id users.id%TYPE := :OLD.id;
BEGIN
  IF EXISTS (SELECT 1 FROM orders WHERE user_id = user_id) THEN
    RAISE_APPLICATION_ERROR(-20001, 'Cannot delete user with open orders');
  END IF;
END;

该触发器定义了在从 users 表中删除一行后，检查该用户是否还有未处理的订单。如果有，则防止删除该用户，并抛出错误消息。

3. 基于视图插入数据

CREATE TABLE employees (
    employee_id NUMBER,
    first_name VARCHAR2(100),
    last_name VARCHAR2(100),
    hire_date DATE,
    salary NUMBER
);

CREATE OR REPLACE VIEW employees_view AS
SELECT employee_id, first_name || ' ' || last_name AS full_name, hire_date, salary
FROM employees;

CREATE OR REPLACE TRIGGER insert_employee_view
INSTEAD OF INSERT ON employees_view
FOR EACH ROW
BEGIN
  INSERT INTO employees(employee_id, first_name, last_name, hire_date, salary)
  VALUES (:NEW.employee_id,
          REGEXP_SUBSTR(:NEW.full_name, '[^-]+', 1, 1),
          REGEXP_SUBSTR(:NEW.full_name, '[^-]+', 1, 2),
          :NEW.hire_date,
          :NEW.salary);
END;

该触发器定义了通过视图 employees_view 插入数据时，将数据插入到基础表 employees 中。触发器将 full_name 字段分解为 first_name 和 last_name，并将其插入基础表。