PostgreSQL 触发器

触发器（Trigger）是一种特殊的存储过程，当对表执行 INSERT、UPDATE、DELETE 或 TRUNCATE 操作时，触发器会自动执行。触发器广泛用于数据验证、审计日志、数据同步等场景。

触发器概述

什么是触发器？

触发器是与表关联的特殊函数，在特定事件发生时自动执行。触发器可以帮助你：

• 数据验证：在插入或更新前检查数据有效性
• 审计日志：记录数据变更历史
• 数据同步：自动更新相关表的数据
• 业务规则：强制执行复杂的业务约束

触发器的执行时机

时机	说明	适用场景
BEFORE	操作执行前触发	数据验证、数据修改
AFTER	操作执行后触发	审计日志、通知
INSTEAD OF	替代操作执行（仅视图）	可更新视图

触发器的级别

级别	说明	执行次数
FOR EACH ROW	行级触发器	每受影响一行执行一次
FOR EACH STATEMENT	语句级触发器	每条 SQL 语句执行一次

创建触发器

基本语法

-- 步骤1：创建触发器函数
CREATE FUNCTION trigger_function_name()
RETURNS TRIGGER AS $$
BEGIN
    -- 触发器逻辑
    RETURN NEW;  -- 或 OLD, NULL
END;
$$ LANGUAGE plpgsql;

-- 步骤2：创建触发器
CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE | TRUNCATE}
ON table_name
[FOR EACH ROW | FOR EACH STATEMENT]
EXECUTE FUNCTION trigger_function_name();

简单示例：自动更新时间戳

-- 创建测试表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

-- 创建触发器
CREATE TRIGGER trigger_update_timestamp
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

-- 测试
INSERT INTO products (name, price) VALUES ('商品A', 99.99);

-- 等待几秒后更新
SELECT pg_sleep(2);
UPDATE products SET price = 89.99 WHERE name = '商品A';

-- 查看结果：updated_at 会自动更新
SELECT name, created_at, updated_at FROM products;

触发器函数

NEW 和 OLD 变量

在行级触发器中，可以访问特殊变量：

变量	说明	可用操作
NEW	新行数据（INSERT/UPDATE 后的值）	INSERT、UPDATE
OLD	旧行数据（UPDATE/DELETE 前的值）	UPDATE、DELETE

CREATE OR REPLACE FUNCTION log_product_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        RAISE NOTICE '插入新产品: %, 价格: %', NEW.name, NEW.price;
    ELSIF TG_OP = 'UPDATE' THEN
        RAISE NOTICE '产品更新: %, 旧价格: %, 新价格: %', 
            OLD.name, OLD.price, NEW.price;
    ELSIF TG_OP = 'DELETE' THEN
        RAISE NOTICE '删除产品: %, 价格: %', OLD.name, OLD.price;
    END IF;
    
    RETURN NEW;  -- DELETE 时应返回 OLD
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_log_changes
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION log_product_changes();

触发器特殊变量

变量	说明
TG_OP	触发操作类型（INSERT/UPDATE/DELETE/TRUNCATE）
TG_TABLE_NAME	触发表名称
TG_TABLE_SCHEMA	触发表所在的 schema
TG_WHEN	触发时机（BEFORE/AFTER/INSTEAD OF）
TG_LEVEL	触发级别（ROW/STATEMENT）
TG_NARGS	触发器参数个数
TG_ARGV[]	触发器参数数组

CREATE OR REPLACE FUNCTION trigger_info()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE '触发器信息:';
    RAISE NOTICE '  操作: %', TG_OP;
    RAISE NOTICE '  表名: %.%', TG_TABLE_SCHEMA, TG_TABLE_NAME;
    RAISE NOTICE '  时机: %', TG_WHEN;
    RAISE NOTICE '  级别: %', TG_LEVEL;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

触发器应用场景

1. 数据验证

-- 创建订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 验证订单金额
CREATE OR REPLACE FUNCTION validate_order()
RETURNS TRIGGER AS $$
BEGIN
    -- 检查金额必须大于零
    IF NEW.total_amount <= 0 THEN
        RAISE EXCEPTION '订单金额必须大于零';
    END IF;
    
    -- 检查状态是否有效
    IF NEW.status NOT IN ('pending', 'processing', 'completed', 'cancelled') THEN
        RAISE EXCEPTION '无效的订单状态: %', NEW.status;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_validate_order
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION validate_order();

2. 审计日志

-- 创建审计日志表
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    operation VARCHAR(10) NOT NULL,
    record_id INTEGER,
    old_data JSONB,
    new_data JSONB,
    changed_by VARCHAR(50) DEFAULT CURRENT_USER,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建通用审计触发器函数
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, operation, record_id, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, NEW.id, to_jsonb(NEW));
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, record_id, old_data, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, NEW.id, to_jsonb(OLD), to_jsonb(NEW));
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, record_id, old_data)
        VALUES (TG_TABLE_NAME, TG_OP, OLD.id, to_jsonb(OLD));
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 在 products 表上创建审计触发器
CREATE TRIGGER trigger_audit_products
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION audit_trigger_func();

3. 自动计算字段

-- 创建订单明细表
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER,
    quantity INTEGER NOT NULL,
    unit_price NUMERIC(10, 2) NOT NULL,
    subtotal NUMERIC(10, 2)  -- 自动计算
);

-- 自动计算小计
CREATE OR REPLACE FUNCTION calculate_subtotal()
RETURNS TRIGGER AS $$
BEGIN
    NEW.subtotal = NEW.quantity * NEW.unit_price;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_calculate_subtotal
BEFORE INSERT OR UPDATE ON order_items
FOR EACH ROW
EXECUTE FUNCTION calculate_subtotal();

-- 插入时自动计算
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 1, 5, 29.99);

SELECT * FROM order_items;
-- subtotal 自动为 149.95

4. 更新汇总表

-- 创建客户汇总表
CREATE TABLE customer_summary (
    customer_id INTEGER PRIMARY KEY,
    total_orders INTEGER DEFAULT 0,
    total_amount NUMERIC(12, 2) DEFAULT 0,
    last_order_date DATE
);

-- 初始化汇总数据
INSERT INTO customer_summary (customer_id)
SELECT id FROM customers;

-- 更新汇总触发器
CREATE OR REPLACE FUNCTION update_customer_summary()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO customer_summary (customer_id, total_orders, total_amount, last_order_date)
        VALUES (NEW.customer_id, 1, NEW.total_amount, NEW.created_at::date)
        ON CONFLICT (customer_id) DO UPDATE SET
            total_orders = customer_summary.total_orders + 1,
            total_amount = customer_summary.total_amount + NEW.total_amount,
            last_order_date = GREATEST(customer_summary.last_order_date, NEW.created_at::date);
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE customer_summary SET
            total_amount = total_amount - OLD.total_amount + NEW.total_amount
        WHERE customer_id = NEW.customer_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE customer_summary SET
            total_orders = total_orders - 1,
            total_amount = total_amount - OLD.total_amount
        WHERE customer_id = OLD.customer_id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_summary
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_customer_summary();

5. 软删除

-- 添加软删除字段
ALTER TABLE products ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE products ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;

-- 软删除触发器
CREATE OR REPLACE FUNCTION soft_delete()
RETURNS TRIGGER AS $$
BEGIN
    -- 不执行真正的删除，而是标记为已删除
    UPDATE products 
    SET deleted_at = CURRENT_TIMESTAMP, is_deleted = TRUE
    WHERE id = OLD.id;
    
    RETURN NULL;  -- 返回 NULL 取消实际删除操作
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_soft_delete
BEFORE DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION soft_delete();

6. 数据同步

-- 同步到历史表
CREATE TABLE orders_archive (LIKE orders);

CREATE OR REPLACE FUNCTION archive_order()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO orders_archive SELECT OLD.*;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_archive_order
BEFORE DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION archive_order();

条件触发器

WHEN 条件

使用 WHEN 子句可以限制触发器只在特定条件下执行：

-- 只在价格变化超过 10% 时记录
CREATE OR REPLACE FUNCTION log_price_change()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO price_history (product_id, old_price, new_price, changed_at)
    VALUES (OLD.id, OLD.price, NEW.price, CURRENT_TIMESTAMP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_log_price_change
AFTER UPDATE ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)
EXECUTE FUNCTION log_price_change();

-- 只在订单状态变为 completed 时触发
CREATE TRIGGER trigger_order_completed
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status != 'completed' AND NEW.status = 'completed')
EXECUTE FUNCTION process_completed_order();

基于列的触发器

-- 只在特定列更新时触发
CREATE TRIGGER trigger_price_update
AFTER UPDATE OF price ON products
FOR EACH ROW
EXECUTE FUNCTION handle_price_update();

INSTEAD OF 触发器

INSTEAD OF 触发器用于使视图可更新：

-- 创建视图
CREATE VIEW customer_order_view AS
SELECT 
    c.id AS customer_id,
    c.name,
    c.email,
    o.id AS order_id,
    o.total_amount,
    o.status
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

-- 创建 INSTEAD OF 触发器
CREATE OR REPLACE FUNCTION instead_of_insert_order()
RETURNS TRIGGER AS $$
BEGIN
    -- 检查客户是否存在，不存在则创建
    INSERT INTO customers (id, name, email)
    VALUES (NEW.customer_id, NEW.name, NEW.email)
    ON CONFLICT (id) DO UPDATE SET
        name = EXCLUDED.name,
        email = EXCLUDED.email;
    
    -- 插入订单
    INSERT INTO orders (customer_id, total_amount, status)
    VALUES (NEW.customer_id, NEW.total_amount, NEW.status);
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_insert_order_view
INSTEAD OF INSERT ON customer_order_view
FOR EACH ROW
EXECUTE FUNCTION instead_of_insert_order();

-- 现在可以向视图插入数据
INSERT INTO customer_order_view (customer_id, name, email, total_amount, status)
VALUES (100, '新客户', '[email protected]', 500, 'pending');

语句级触发器

语句级触发器对每条 SQL 语句执行一次，而不是每行执行一次：

-- 记录批量操作日志
CREATE OR REPLACE FUNCTION log_bulk_operation()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO operation_log (operation, table_name, executed_at)
    VALUES (TG_OP, TG_TABLE_NAME, CURRENT_TIMESTAMP);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_log_bulk
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH STATEMENT
EXECUTE FUNCTION log_bulk_operation();

管理触发器

查看触发器

-- 查看表上的触发器
\d+ products

-- 查询系统表
SELECT 
    trigger_name,
    event_manipulation,
    event_object_table,
    action_timing,
    action_orientation
FROM information_schema.triggers
WHERE event_object_table = 'products';

-- 查看触发器定义
SELECT pg_get_triggerdef(oid) 
FROM pg_trigger 
WHERE tgname = 'trigger_name';

启用和禁用触发器

-- 禁用特定触发器
ALTER TABLE products DISABLE TRIGGER trigger_update_timestamp;

-- 禁用表上所有触发器
ALTER TABLE products DISABLE TRIGGER ALL;

-- 启用触发器
ALTER TABLE products ENABLE TRIGGER trigger_update_timestamp;

-- 启用所有触发器
ALTER TABLE products ENABLE TRIGGER ALL;

-- 启用 REPLICA 模式（用于复制）
ALTER TABLE products ENABLE REPLICA TRIGGER trigger_update_timestamp;

-- 启用 ALWAYS 模式（总是触发，包括复制）
ALTER TABLE products ENABLE ALWAYS TRIGGER trigger_update_timestamp;

删除触发器

-- 删除触发器
DROP TRIGGER IF EXISTS trigger_name ON table_name;

-- 级联删除依赖对象
DROP TRIGGER trigger_name ON table_name CASCADE;

触发器性能考虑

1. 避免触发器中的复杂操作

-- 不好的做法：触发器中执行复杂查询
CREATE OR REPLACE FUNCTION bad_trigger()
RETURNS TRIGGER AS $$
BEGIN
    -- 每次插入都执行复杂查询
    PERFORM COUNT(*) FROM large_table WHERE ...;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 好的做法：使用条件触发器减少执行次数
CREATE TRIGGER trigger_good
AFTER UPDATE ON table_name
FOR EACH ROW
WHEN (NEW.status != OLD.status)  -- 只在状态变化时触发
EXECUTE FUNCTION good_trigger();

2. 使用 WHEN 条件优化

-- 只在必要时触发
CREATE TRIGGER trigger_update_total
AFTER INSERT OR UPDATE OF quantity OR UPDATE OF unit_price ON order_items
FOR EACH ROW
WHEN (NEW.quantity != OLD.quantity OR NEW.unit_price != OLD.unit_price)
EXECUTE FUNCTION update_order_total();

3. 语句级触发器替代行级触发器

对于批量操作，使用语句级触发器或过渡表：

-- 使用过渡表（PostgreSQL 10+）
CREATE OR REPLACE FUNCTION process_bulk_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- 可以访问所有受影响的行
    INSERT INTO change_log (record_ids)
    SELECT array_agg(id) FROM new_table;  -- new_table 是过渡表
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_bulk_changes
AFTER UPDATE ON products
REFERENCING NEW TABLE AS new_table OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE FUNCTION process_bulk_changes();

小结

本章我们学习了：

1. 触发器概念：自动执行的存储过程
2. 触发时机：BEFORE、AFTER、INSTEAD OF
3. 触发级别：行级和语句级
4. NEW 和 OLD 变量：访问操作前后的数据
5. 常见应用：数据验证、审计日志、自动计算、数据同步
6. 条件触发器：使用 WHEN 条件优化性能
7. INSTEAD OF 触发器：使视图可更新
8. 触发器管理：创建、查看、启用/禁用、删除

练习

1. 创建一个触发器，在用户表插入数据时自动创建用户配置记录
2. 创建一个审计触发器，记录所有订单状态变更
3. 创建一个 INSTEAD OF 触发器，使复杂视图可更新
4. 使用 WHEN 条件优化触发器性能

参考资源

• PostgreSQL 触发器官方文档
• PostgreSQL CREATE TRIGGER 官方文档
• PostgreSQL 触发器函数官方文档