Oracle（39）什么是触发器（Trigger）？如何创建触发器？

最新推荐文章于 2024-09-25 17:49:34 发布

辞暮尔尔-烟火年年

最新推荐文章于 2024-09-25 17:49:34 发布

阅读量534

点赞数 7

分类专栏： Oracle 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/qq_43012298/article/details/139440338

版权

Oracle 专栏收录该内容

149 篇文章 5 订阅

订阅专栏

触发器（Trigger）是数据库中一种特殊的存储过程，它在特定的数据库事件（如INSERT、UPDATE或DELETE操作）发生时自动执行。触发器可以用于实现复杂的数据完整性约束、审计、日志记录和自动化任务等功能。

触发器的类型

触发器按触发事件和触发时间可以分为以下几种类型：

按触发事件：
- INSERT触发器：在插入数据时触发。
- UPDATE触发器：在更新数据时触发。
- DELETE触发器：在删除数据时触发。
按触发时间：
- BEFORE触发器：在事件发生之前触发。
- AFTER触发器：在事件发生之后触发。

创建触发器的步骤

定义触发器的名称。
指定触发事件（INSERT、UPDATE、DELETE）。
指定触发时间（BEFORE、AFTER）。
指定触发的表。
编写触发器的执行逻辑。

代码示例

下面的示例展示了如何创建一个触发器，该触发器在 employees 表插入数据之前检查薪水是否大于零，如果不是则抛出异常。同时，我们还展示了如何创建一个在 employees 表更新数据之后记录修改信息的触发器。

1. 创建表和插入示例数据

首先，创建一个测试表 employees。

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    salary NUMBER
);

-- 创建日志表，用于记录更新操作
CREATE TABLE employees_log (
    log_id NUMBER PRIMARY KEY,
    employee_id NUMBER,
    old_salary NUMBER,
    new_salary NUMBER,
    update_date DATE
);

COMMIT;

2. 创建 BEFORE INSERT 触发器

这个触发器在向 employees 表插入数据之前检查薪水是否大于零，如果不是则抛出异常。

CREATE OR REPLACE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary <= 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary must be greater than zero');
    END IF;
END;
/

3. 创建 AFTER UPDATE 触发器

这个触发器在更新 employees 表之后记录修改信息到 employees_log 表。

CREATE OR REPLACE TRIGGER after_update_employee
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_log (log_id, employee_id, old_salary, new_salary, update_date)
    VALUES (employees_log_seq.NEXTVAL, :OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

4. 测试触发器

测试 BEFORE INSERT 触发器

尝试插入一条薪水小于等于零的记录，应该会触发异常。

BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, salary)
    VALUES (1, 'John', 'Doe', -5000);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

输出结果应该是：

ORA-20001: Salary must be greater than zero

测试 AFTER UPDATE 触发器

更新一条记录并查看日志表。

-- 插入一条有效记录
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 5000);

-- 更新记录
UPDATE employees
SET salary = 6000
WHERE employee_id = 1;

-- 查看日志表
SELECT * FROM employees_log;

输出结果应该是：

LOG_ID | EMPLOYEE_ID | OLD_SALARY | NEW_SALARY | UPDATE_DATE
-------|-------------|------------|------------|-------------
1      | 1           | 5000       | 6000       | <更新日期>