PostgreSQL触发器

概述

触发器是当特定事件（INSERT、UPDATE、DELETE、TRUNCATE）发生时自动执行的函数。触发器可用于数据验证、审计日志、自动计算等场景。

触发器类型

类型	说明
BEFORE	在操作执行之前触发
AFTER	在操作执行之后触发
INSTEAD OF	替代操作执行（仅用于视图）
FOR EACH ROW	每行触发一次
FOR EACH STATEMENT	每语句触发一次

创建触发器

基本步骤

1. 创建触发器函数
2. 创建触发器

触发器函数

CREATE OR REPLACE FUNCTION trigger_function_name()
RETURNS TRIGGER AS $$
BEGIN
    -- 触发器逻辑
    RETURN NEW;  -- 对于BEFORE触发器返回NEW或NULL
END;
$$ LANGUAGE plpgsql;

创建触发器语法

CREATE TRIGGER trigger_name
    {BEFORE | AFTER | INSTEAD OF}
    {INSERT | UPDATE | DELETE | TRUNCATE}
    ON table_name
    [FOR EACH {ROW | STATEMENT}]
    [WHEN (condition)]
    EXECUTE FUNCTION trigger_function_name();

触发器示例

自动更新时间戳

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

-- 创建触发器
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

审计日志

-- 审计表
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(50),
    operation VARCHAR(10),
    old_data JSONB,
    new_data JSONB,
    changed_by VARCHAR(50),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 触发器函数
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, operation, new_data, changed_by)
        VALUES (TG_TABLE_NAME, 'INSERT', row_to_json(NEW), current_user);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_by)
        VALUES (TG_TABLE_NAME, 'UPDATE', row_to_json(OLD), row_to_json(NEW), current_user);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, old_data, changed_by)
        VALUES (TG_TABLE_NAME, 'DELETE', row_to_json(OLD), current_user);
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 为表创建触发器
CREATE TRIGGER users_audit
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger();

数据验证

-- 验证邮箱格式
CREATE OR REPLACE FUNCTION validate_email()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
        RAISE EXCEPTION 'Invalid email format: %', NEW.email;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_email
    BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION validate_email();

自动计算

-- 自动计算订单总额
CREATE OR REPLACE FUNCTION calculate_order_total()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE orders
    SET total_amount = (
        SELECT COALESCE(SUM(quantity * unit_price), 0)
        FROM order_items
        WHERE order_id = NEW.order_id
    )
    WHERE id = NEW.order_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_order_total
    AFTER INSERT OR UPDATE OR DELETE ON order_items
    FOR EACH ROW
    EXECUTE FUNCTION calculate_order_total();

触发器中的特殊变量

变量	说明
NEW	INSERT/UPDATE时的新行数据
OLD	UPDATE/DELETE时的旧行数据
TG_NAME	触发器名称
TG_TABLE_NAME	触发器所在表名
TG_OP	操作类型（INSERT/UPDATE/DELETE/TRUNCATE）
TG_WHEN	触发时机（BEFORE/AFTER/INSTEAD OF）
TG_LEVEL	触发级别（ROW/STATEMENT）

条件触发器

使用WHEN子句指定触发条件：

-- 只在状态改变时触发
CREATE TRIGGER status_change_trigger
    AFTER UPDATE ON orders
    FOR EACH ROW
    WHEN (OLD.status IS DISTINCT FROM NEW.status)
    EXECUTE FUNCTION notify_status_change();

-- 只在价格变化时触发
CREATE TRIGGER price_change_trigger
    BEFORE UPDATE ON products
    FOR EACH ROW
    WHEN (OLD.price <> NEW.price)
    EXECUTE FUNCTION log_price_change();

管理触发器

查看触发器

-- 查看表的触发器
SELECT trigger_name, event_manipulation, action_timing
FROM information_schema.triggers
WHERE event_object_table = 'users';

-- 使用psql命令
\dS+ users

禁用和启用触发器

-- 禁用单个触发器
ALTER TABLE users DISABLE TRIGGER users_audit;

-- 禁用所有触发器
ALTER TABLE users DISABLE TRIGGER ALL;

-- 启用触发器
ALTER TABLE users ENABLE TRIGGER users_audit;
ALTER TABLE users ENABLE TRIGGER ALL;

删除触发器

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

-- 如果存在则删除
DROP TRIGGER IF EXISTS trigger_name ON table_name;

INSTEAD OF触发器（视图）

-- 创建视图
CREATE VIEW user_profiles AS
SELECT u.id, u.username, p.bio, p.avatar
FROM users u
LEFT JOIN profiles p ON u.id = p.user_id;

-- 为视图创建INSTEAD OF触发器
CREATE OR REPLACE FUNCTION update_user_profile()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE users SET username = NEW.username WHERE id = NEW.id;
    UPDATE profiles SET bio = NEW.bio, avatar = NEW.avatar
    WHERE user_id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_profile_trigger
    INSTEAD OF UPDATE ON user_profiles
    FOR EACH ROW
    EXECUTE FUNCTION update_user_profile();

事件触发器

PostgreSQL还支持数据库级别的事件触发器：

-- 记录DDL操作
CREATE OR REPLACE FUNCTION log_ddl_command()
RETURNS event_trigger AS $$
BEGIN
    INSERT INTO ddl_log (command_tag, object_type, schema_name, object_identity)
    SELECT tg_tag, object_type, schema_name, object_identity
    FROM pg_event_trigger_ddl_commands();
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER log_ddl
    ON ddl_command_end
    EXECUTE FUNCTION log_ddl_command();

最佳实践

1. 保持简单：触发器函数应该简洁高效
2. 避免递归：小心触发器触发其他触发器
3. 错误处理：使用EXCEPTION块处理错误
4. 性能考虑：触发器会影响DML操作性能
5. 文档化：记录触发器的目的和行为
6. 测试：充分测试触发器的各种场景