PostgreSQL中如何使用触发器

在 PostgreSQL 中，触发器（Trigger）是一种特殊的函数，它在特定的数据库事件（如 INSERT、UPDATE 或 DELETE）发生时自动执行。以下是如何在 PostgreSQL 中创建和使用触发器的步骤：

1. 创建触发器函数

首先，你需要创建一个触发器函数，该函数将在触发器被激活时执行。

CREATE OR REPLACE FUNCTION trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- 在这里编写触发器逻辑
    -- 例如，记录日志、更新其他表等
    RETURN NEW; -- 对于 INSERT 和 UPDATE 触发器，返回 NEW；对于 DELETE 触发器，返回 OLD
END;
$$ LANGUAGE plpgsql;

2. 创建触发器

接下来，你需要创建一个触发器，并将其与特定的表和事件关联起来。

CREATE TRIGGER trigger_name
AFTER INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
EXECUTE FUNCTION trigger_function();

• trigger_name：触发器的名称。
• AFTER INSERT OR UPDATE OR DELETE：指定触发器在 INSERT、UPDATE 或 DELETE 事件之后执行。
• ON table_name：指定触发器作用的表。
• FOR EACH ROW：指定触发器对每一行数据都执行一次。
• EXECUTE FUNCTION trigger_function()：指定触发器执行的函数。

示例

假设我们有一个名为 employees 的表，我们希望在每次插入或更新员工记录时，自动更新一个日志表 employee_logs。

创建日志表

CREATE TABLE employee_logs (
    log_id SERIAL PRIMARY KEY,
    employee_id INT,
    action VARCHAR(10),
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

创建触发器函数

CREATE OR REPLACE FUNCTION update_employee_log()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO employee_logs (employee_id, action) VALUES (NEW.id, 'INSERT');
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO employee_logs (employee_id, action) VALUES (NEW.id, 'UPDATE');
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO employee_logs (employee_id, action) VALUES (OLD.id, 'DELETE');
    END IF;
    RETURN NEW; -- 对于 INSERT 和 UPDATE 触发器，返回 NEW；对于 DELETE 触发器，返回 OLD
END;
$$ LANGUAGE plpgsql;

创建触发器

CREATE TRIGGER employee_log_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_employee_log();

测试触发器

现在，你可以测试触发器是否按预期工作。

-- 插入一条记录
INSERT INTO employees (name, position) VALUES ('John Doe', 'Developer');

-- 更新一条记录
UPDATE employees SET position = 'Senior Developer' WHERE name = 'John Doe';

-- 删除一条记录
DELETE FROM employees WHERE name = 'John Doe';

每次执行这些操作后，employee_logs 表都会相应地更新。

通过这种方式，你可以在 PostgreSQL 中创建和使用触发器来自动化各种数据库操作。