PostgreSQL教程

触发器的工作原理

触发器的工作流程如下：

1. 用户执行触发事件的操作（INSERT、UPDATE、DELETE等）
2. 数据库检查是否存在与该表和事件相关的触发器
3. 如果存在触发器，则按照定义的时机和顺序执行触发器函数
4. 触发器函数执行完毕后，继续执行原始操作
5. 如果触发器函数中出现错误，则可能回滚整个操作

按事件类型分类

-- 行级触发器 - 对每一行执行一次
CREATE TRIGGER row_trigger
    BEFORE INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION log_user_insert();

-- 语句级触发器 - 对整个SQL语句执行一次
CREATE TRIGGER statement_trigger
    AFTER INSERT ON users
    FOR EACH STATEMENT
    EXECUTE FUNCTION log_user_insert_summary();

两种类型的触发器区别：

按触发时机分类

-- BEFORE触发器 - 在事件发生之前执行
CREATE TRIGGER before_insert_trigger
    BEFORE INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION validate_user_data();

-- AFTER触发器 - 在事件发生之后执行
CREATE TRIGGER after_insert_trigger
    AFTER INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION send_welcome_email();

-- INSTEAD OF触发器 - 替代原始操作执行（主要用于视图）
CREATE TRIGGER instead_of_insert_trigger
    INSTEAD OF INSERT ON user_view
    FOR EACH ROW
    EXECUTE FUNCTION handle_view_insert();

三种触发时机的特点：

数据完整性约束

-- 确保员工薪资不能低于最低工资标准
CREATE OR REPLACE FUNCTION check_minimum_salary()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary < 1500 THEN
        RAISE EXCEPTION '薪资不能低于最低工资标准1500元';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER salary_check_trigger
    BEFORE INSERT OR UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION check_minimum_salary();

审计日志记录

-- 创建审计日志表
CREATE TABLE user_audit_log (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    operation VARCHAR(10),
    old_values JSONB,
    new_values JSONB,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_name TEXT
);

-- 记录用户表的变更日志
CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO user_audit_log (user_id, operation, old_values, user_name)
        VALUES (OLD.id, TG_OP, row_to_json(OLD), USER);
        RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO user_audit_log (user_id, operation, new_values, user_name)
        VALUES (NEW.id, TG_OP, row_to_json(NEW), USER);
        RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO user_audit_log (user_id, operation, old_values, new_values, user_name)
        VALUES (NEW.id, TG_OP, row_to_json(OLD), row_to_json(NEW), USER);
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION log_user_changes();

数据同步

-- 在用户表插入新记录时，同步到历史表
CREATE OR REPLACE FUNCTION sync_user_to_history()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_history (user_id, username, email, created_at, sync_time)
    VALUES (NEW.id, NEW.username, NEW.email, NEW.created_at, CURRENT_TIMESTAMP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_sync_trigger
    AFTER INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION sync_user_to_history();

复杂业务逻辑

-- 订单创建时自动更新库存
CREATE OR REPLACE FUNCTION update_inventory_on_order()
RETURNS TRIGGER AS $$
DECLARE
    product_record RECORD;
BEGIN
    -- 遍历订单项
    FOR product_record IN 
        SELECT product_id, quantity 
        FROM order_items 
        WHERE order_id = NEW.id
    LOOP
        -- 更新产品库存
        UPDATE products 
        SET stock_quantity = stock_quantity - product_record.quantity,
            last_updated = CURRENT_TIMESTAMP
        WHERE id = product_record.product_id;
        
        -- 检查库存是否足够
        IF (SELECT stock_quantity FROM products WHERE id = product_record.product_id) < 0 THEN
            RAISE EXCEPTION '产品ID % 库存不足', product_record.product_id;
        END IF;
    END LOOP;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_inventory_trigger
    AFTER INSERT ON orders
    FOR EACH ROW
    EXECUTE FUNCTION update_inventory_on_order();

优点

• 自动化执行：无需应用程序显式调用，自动响应数据库事件
• 数据一致性：确保在所有情况下都执行关键的业务规则
• 集中管理：业务逻辑集中在数据库层，便于维护
• 性能优化：在数据库服务器上执行，减少网络往返
• 安全性：隐藏复杂逻辑，防止应用程序绕过关键约束

缺点

• 调试困难：触发器的自动执行特性使得问题排查变得复杂
• 性能影响：不恰当的触发器可能严重影响数据库性能
• 维护复杂：触发器之间的依赖关系可能使维护变得困难
• 移植性差：不同数据库系统的触发器语法和特性可能不同
• 意外副作用：触发器可能产生意想不到的级联效应