從多個方面闡述 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，並將其插入基礎表。