Oracle中怎样使用触发器？

用心去追梦

于 2024-09-28 11:37:03 发布

阅读量207

点赞数 3

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/qq_33240556/article/details/142613322

版权

在Oracle数据库中，触发器（Trigger）是一种特殊的存储过程，它会在特定的数据库事件发生时自动执行。触发器可以用于多种用途，比如维护复杂的业务规则、实施数据完整性约束、记录日志等。下面是如何创建和使用触发器的基本步骤和示例。

创建触发器

1. 基本语法

创建触发器的基本语法如下：

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT [OR] | UPDATE [OF column[, ...]] [OR] | DELETE}
ON table_name
[FOR EACH ROW]
[WHEN (condition)]
BEGIN
  -- PL/SQL block to execute
END;

trigger_name：触发器的名字。
{BEFORE | AFTER | INSTEAD OF}：指定触发器是在事件之前、之后还是代替事件执行。
{INSERT | UPDATE | DELETE}：指定触发器响应的DML操作。
table_name：触发器关联的表名。
[FOR EACH ROW]：如果存在，则触发器是行级的；否则是语句级的。
[WHEN (condition)]：可选条件，只有当这个条件为真时触发器才会被激活。
BEGIN...END;：PL/SQL块，包含要执行的逻辑。

2. 示例

a. 行级触发器 - 在插入后记录审计日志

假设我们有一个employees表，并希望每当插入新员工时，在另一个audit_log表中记录这次插入操作。

-- 创建审计日志表
CREATE TABLE audit_log (
    log_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    action VARCHAR2(10),
    user_id VARCHAR2(30),
    timestamp TIMESTAMP
);

-- 创建触发器
CREATE OR REPLACE TRIGGER trg_audit_insert_employee
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (action, user_id, timestamp)
  VALUES ('INSERT', USER, SYSDATE);
END;
/

在这个例子中，每当向employees表插入一条新记录时，触发器trg_audit_insert_employee就会在audit_log表中插入一条审计记录。

b. 行级触发器 - 更新前验证数据

假设我们有一个orders表，并且我们希望在更新订单状态之前验证新的状态是否有效。

-- 创建订单表
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    order_date DATE,
    status VARCHAR2(20) CHECK (status IN ('NEW', 'IN_PROGRESS', 'SHIPPED', 'CANCELLED'))
);

-- 创建触发器
CREATE OR REPLACE TRIGGER trg_validate_order_status
BEFORE UPDATE OF status ON orders
FOR EACH ROW
BEGIN
  IF :NEW.status NOT IN ('NEW', 'IN_PROGRESS', 'SHIPPED', 'CANCELLED') THEN
    RAISE_APPLICATION_ERROR(-20001, 'Invalid order status: ' || :NEW.status);
  END IF;
END;
/

在这个例子中，触发器trg_validate_order_status会在更新orders表的状态字段之前检查新状态的有效性。如果新状态不在允许的列表中，将抛出一个错误。

c. 语句级触发器 - 记录删除操作

假设我们有一个products表，并希望记录每次删除产品的时间戳。

-- 创建产品表
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(50),
    delete_timestamp TIMESTAMP
);

-- 创建触发器
CREATE OR REPLACE TRIGGER trg_record_product_deletion
BEFORE DELETE ON products
BEGIN
  UPDATE products
  SET delete_timestamp = SYSDATE
  WHERE product_id IN (SELECT product_id FROM deleted_products);
END;
/

在这个例子中，触发器trg_record_product_deletion会在删除products表中的记录之前更新delete_timestamp字段。

d. 替代触发器 - 在视图上实现更新操作

假设我们有一个名为employee_view的视图，我们希望在视图上执行更新操作时，实际更新的是基础表employees。

-- 创建基础表
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    salary NUMBER
);

-- 创建视图
CREATE OR REPLACE VIEW employee_view AS
SELECT employee_id, first_name, last_name, salary
FROM employees;

-- 创建替代触发器
CREATE OR REPLACE TRIGGER trg_update_employee_view
INSTEAD OF UPDATE ON employee_view
FOR EACH ROW
BEGIN
  UPDATE employees
  SET salary = :NEW.salary
  WHERE employee_id = :OLD.employee_id;
END;
/