触发器

概述

触发器（Trigger）是数据库管理系统中的一种特殊类型的存储过程，它会在特定的数据库事件（如插入、更新或删除操作）发生时自动执行。触发器通常用于维护数据的完整性、审计日志、自动更新相关表等场景。

触发器的基本概念

触发器的工作原理

触发器由以下几个部分组成：

1. 触发事件：触发器在特定事件发生时被触发，如 INSERT、UPDATE、DELETE。
2. 触发时间：触发器可以在事件发生前（BEFORE）或事件发生后（AFTER）执行。
3. 触发条件：触发器可以包含条件，只有满足条件时才会执行。
4. 触发操作：触发器执行的操作，通常是一个PL/pgSQL函数。

触发器的类型

• 行级触发器：在每一行数据发生变化时触发。
• 语句级触发器：在整个SQL语句执行完毕后触发一次。

创建触发器

语法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH {ROW | STATEMENT}
EXECUTE FUNCTION function_name();

示例

假设我们有一个名为 employees 的表，我们希望在插入新员工时自动更新 last_updated 字段。

-- 创建表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    last_updated TIMESTAMP
);

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_last_updated()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_updated = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER update_last_updated_trigger
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION update_last_updated();

解释

• CREATE TRIGGER update_last_updated_trigger：创建一个名为 update_last_updated_trigger 的触发器。
• BEFORE INSERT ON employees：在 employees 表的 INSERT 操作之前触发。
• FOR EACH ROW：对每一行数据执行触发器。
• EXECUTE FUNCTION update_last_updated()：执行 update_last_updated 函数。

实践练习

练习1：创建一个触发器来记录删除操作

1. 创建一个名为 logs 的表，用于记录删除操作。
2. 创建一个触发器，在 employees 表的 DELETE 操作后，将删除的记录插入到 logs 表中。

-- 创建 logs 表
CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    employee_id INT,
    deleted_at TIMESTAMP
);

-- 创建触发器函数
CREATE OR REPLACE FUNCTION log_delete()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO logs (employee_id, deleted_at)
    VALUES (OLD.id, NOW());
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER log_delete_trigger
AFTER DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_delete();

练习2：创建一个触发器来检查插入数据的合法性

1. 创建一个触发器，在插入新员工时检查 name 字段是否为空，如果为空则抛出错误。

-- 创建触发器函数
CREATE OR REPLACE FUNCTION check_name()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.name IS NULL OR NEW.name = '' THEN
        RAISE EXCEPTION 'Name cannot be empty';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER check_name_trigger
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION check_name();

总结

触发器是PostgreSQL中一个强大的工具，可以自动化许多数据库操作。通过本教程，你应该已经掌握了如何创建和使用触发器，并能够在实际项目中应用它们。

进一步学习

• 学习如何使用 OLD 和 NEW 关键字在触发器中访问数据。
• 探索如何使用 WHEN 子句来添加触发条件。
• 了解如何禁用和删除触发器。

希望本教程对你有所帮助，祝你在PostgreSQL的学习旅程中取得更多进步！