触发器

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

触发器概述

触发器的类型

类型	说明
BEFORE触发器	在操作执行前触发
AFTER触发器	在操作执行后触发
INSTEAD OF触发器	替代操作执行（用于视图）

触发时机

• FOR EACH ROW：行级触发器，每影响一行触发一次
• FOR EACH STATEMENT：语句级触发器，每条SQL语句触发一次

创建触发器

创建触发器函数

触发器函数必须返回TRIGGER类型：

-- 创建示例表
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    salary NUMERIC(10, 2),
    dept_id INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE TABLE salary_audit (
    audit_id SERIAL PRIMARY KEY,
    emp_id INTEGER,
    old_salary NUMERIC(10, 2),
    new_salary NUMERIC(10, 2),
    changed_by VARCHAR(50),
    changed_at TIMESTAMP
);

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER
AS $$
BEGIN
    -- NEW是即将插入或更新后的行数据
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trg_update_timestamp
BEFORE UPDATE ON employees  -- 在UPDATE之前触发
FOR EACH ROW                -- 每行触发一次
EXECUTE FUNCTION update_timestamp();

BEFORE触发器

-- BEFORE触发器：在操作前执行，可以修改NEW数据
CREATE OR REPLACE FUNCTION validate_salary()
RETURNS TRIGGER
AS $$
BEGIN
    -- 检查薪水是否为负数
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION '薪水不能为负数';
    END IF;
    
    -- 检查薪水是否超过上限
    IF NEW.salary > 100000 THEN
        RAISE EXCEPTION '薪水不能超过100000';
    END IF;
    
    -- 设置创建时间
    IF TG_OP = 'INSERT' THEN
        NEW.created_at = CURRENT_TIMESTAMP;
    END IF;
    
    RETURN NEW;  -- 返回NEW继续执行操作
END;
$$ LANGUAGE plpgsql;

-- 创建BEFORE INSERT或UPDATE触发器
CREATE TRIGGER trg_validate_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION validate_salary();

AFTER触发器

-- AFTER触发器：在操作后执行，用于审计、日志等
CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER
AS $$
BEGIN
    -- 只在薪水变化时记录
    IF TG_OP = 'UPDATE' AND OLD.salary != NEW.salary THEN
        INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_by, changed_at)
        VALUES (
            NEW.emp_id,
            OLD.salary,      -- OLD是更新前的数据
            NEW.salary,
            CURRENT_USER,
            CURRENT_TIMESTAMP
        );
    END IF;
    
    RETURN NEW;  -- AFTER触发器返回值被忽略
END;
$$ LANGUAGE plpgsql;

-- 创建AFTER UPDATE触发器
CREATE TRIGGER trg_log_salary_change
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

触发器条件

-- 使用WHEN条件限制触发器执行
CREATE OR REPLACE FUNCTION notify_high_salary()
RETURNS TRIGGER
AS $$
BEGIN
    RAISE NOTICE '高薪员工: %, 薪水: %', NEW.emp_name, NEW.salary;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 只在薪水超过50000时触发
CREATE TRIGGER trg_notify_high_salary
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
WHEN (NEW.salary > 50000)  -- 条件表达式
EXECUTE FUNCTION notify_high_salary();

触发器特殊变量

在触发器函数中可以使用以下特殊变量：

CREATE OR REPLACE FUNCTION trigger_demo()
RETURNS TRIGGER
AS $$
BEGIN
    -- TG_NAME: 触发器名称
    RAISE NOTICE '触发器名称: %', TG_NAME;
    
    -- TG_WHEN: 触发时机 (BEFORE, AFTER, INSTEAD OF)
    RAISE NOTICE '触发时机: %', TG_WHEN;
    
    -- TG_OP: 触发操作 (INSERT, UPDATE, DELETE, TRUNCATE)
    RAISE NOTICE '触发操作: %', TG_OP;
    
    -- TG_LEVEL: 触发级别 (ROW, STATEMENT)
    RAISE NOTICE '触发级别: %', TG_LEVEL;
    
    -- TG_TABLE_NAME: 表名
    RAISE NOTICE '表名: %', TG_TABLE_NAME;
    
    -- TG_TABLE_SCHEMA: Schema名
    RAISE NOTICE 'Schema: %', TG_TABLE_SCHEMA;
    
    -- NEW: 新数据 (INSERT/UPDATE时可用)
    -- OLD: 旧数据 (UPDATE/DELETE时可用)
    
    IF TG_OP = 'INSERT' THEN
        RAISE NOTICE '插入数据: %', NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        RAISE NOTICE '旧数据: %', OLD;
        RAISE NOTICE '新数据: %', NEW;
    ELSIF TG_OP = 'DELETE' THEN
        RAISE NOTICE '删除数据: %', OLD;
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

INSTEAD OF触发器

INSTEAD OF触发器用于视图，替代原始操作：

-- 创建视图
CREATE VIEW employee_view AS
SELECT 
    emp_id,
    emp_name,
    salary,
    dept_id,
    salary * 12 AS annual_salary
FROM employees;

-- 创建INSTEAD OF触发器函数
CREATE OR REPLACE FUNCTION instead_of_insert_employee()
RETURNS TRIGGER
AS $$
BEGIN
    -- 插入到实际表
    INSERT INTO employees (emp_name, salary, dept_id)
    VALUES (NEW.emp_name, NEW.salary, NEW.dept_id);
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建INSTEAD OF触发器
CREATE TRIGGER trg_instead_of_insert
INSTEAD OF INSERT ON employee_view
FOR EACH ROW
EXECUTE FUNCTION instead_of_insert_employee();

-- 现在可以向视图插入数据
INSERT INTO employee_view (emp_name, salary, dept_id)
VALUES ('测试员工', 10000, 1);

-- INSTEAD OF UPDATE触发器
CREATE OR REPLACE FUNCTION instead_of_update_employee()
RETURNS TRIGGER
AS $$
BEGIN
    UPDATE employees
    SET 
        emp_name = NEW.emp_name,
        salary = NEW.salary,
        dept_id = NEW.dept_id
    WHERE emp_id = NEW.emp_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_instead_of_update
INSTEAD OF UPDATE ON employee_view
FOR EACH ROW
EXECUTE FUNCTION instead_of_update_employee();

语句级触发器

-- 语句级触发器：每条SQL语句只触发一次
CREATE TABLE operation_log (
    log_id SERIAL PRIMARY KEY,
    operation VARCHAR(20),
    table_name VARCHAR(50),
    affected_rows INTEGER,
    operation_time TIMESTAMP,
    user_name VARCHAR(50)
);

CREATE OR REPLACE FUNCTION log_statement_operation()
RETURNS TRIGGER
AS $$
DECLARE
    row_count INTEGER;
BEGIN
    -- 获取受影响的行数
    GET DIAGNOSTICS row_count = ROW_COUNT;
    
    INSERT INTO operation_log (operation, table_name, affected_rows, operation_time, user_name)
    VALUES (TG_OP, TG_TABLE_NAME, row_count, CURRENT_TIMESTAMP, CURRENT_USER);
    
    RETURN NULL;  -- 语句级触发器返回NULL
END;
$$ LANGUAGE plpgsql;

-- 创建语句级触发器
CREATE TRIGGER trg_log_statement
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH STATEMENT  -- 语句级
EXECUTE FUNCTION log_statement_operation();

TRUNCATE触发器

-- TRUNCATE触发器
CREATE OR REPLACE FUNCTION log_truncate()
RETURNS TRIGGER
AS $$
BEGIN
    RAISE NOTICE '表 % 被清空', TG_TABLE_NAME;
    
    INSERT INTO operation_log (operation, table_name, operation_time, user_name)
    VALUES ('TRUNCATE', TG_TABLE_NAME, CURRENT_TIMESTAMP, CURRENT_USER);
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 创建TRUNCATE触发器
CREATE TRIGGER trg_log_truncate
AFTER TRUNCATE ON employees
FOR EACH STATEMENT
EXECUTE FUNCTION log_truncate();

实用触发器示例

自动维护更新时间

-- 通用更新时间触发器函数
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER
AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 应用到多个表
CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER trg_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

数据审计

-- 创建审计表
CREATE TABLE audit_log (
    audit_id SERIAL PRIMARY KEY,
    table_name VARCHAR(50),
    operation VARCHAR(20),
    record_id INTEGER,
    old_data JSONB,
    new_data JSONB,
    changed_by VARCHAR(50),
    changed_at 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, changed_by, changed_at)
        VALUES (
            TG_TABLE_NAME,
            TG_OP,
            NEW.emp_id,
            to_jsonb(NEW),
            CURRENT_USER,
            CURRENT_TIMESTAMP
        );
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, record_id, old_data, new_data, changed_by, changed_at)
        VALUES (
            TG_TABLE_NAME,
            TG_OP,
            NEW.emp_id,
            to_jsonb(OLD),
            to_jsonb(NEW),
            CURRENT_USER,
            CURRENT_TIMESTAMP
        );
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, record_id, old_data, changed_by, changed_at)
        VALUES (
            TG_TABLE_NAME,
            TG_OP,
            OLD.emp_id,
            to_jsonb(OLD),
            CURRENT_USER,
            CURRENT_TIMESTAMP
        );
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- 应用审计触发器
CREATE TRIGGER trg_employees_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION audit_trigger_func();

级联更新

-- 创建部门表
CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL,
    total_employees INTEGER DEFAULT 0,
    total_salary NUMERIC(12, 2) DEFAULT 0
);

-- 更新部门统计的触发器
CREATE OR REPLACE FUNCTION update_dept_stats()
RETURNS TRIGGER
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- 新增员工，更新部门统计
        UPDATE departments
        SET 
            total_employees = total_employees + 1,
            total_salary = total_salary + NEW.salary
        WHERE dept_id = NEW.dept_id;
        
    ELSIF TG_OP = 'UPDATE' THEN
        -- 更新员工，调整部门统计
        IF OLD.dept_id != NEW.dept_id THEN
            -- 部门变更
            UPDATE departments
            SET 
                total_employees = total_employees - 1,
                total_salary = total_salary - OLD.salary
            WHERE dept_id = OLD.dept_id;
            
            UPDATE departments
            SET 
                total_employees = total_employees + 1,
                total_salary = total_salary + NEW.salary
            WHERE dept_id = NEW.dept_id;
        ELSE
            -- 同部门薪资变更
            UPDATE departments
            SET total_salary = total_salary - OLD.salary + NEW.salary
            WHERE dept_id = NEW.dept_id;
        END IF;
        
    ELSIF TG_OP = 'DELETE' THEN
        -- 删除员工，更新部门统计
        UPDATE departments
        SET 
            total_employees = total_employees - 1,
            total_salary = total_salary - OLD.salary
        WHERE dept_id = OLD.dept_id;
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

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

数据验证

-- 业务规则验证触发器
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount NUMERIC(10, 2)
);

CREATE TABLE order_status_history (
    history_id SERIAL PRIMARY KEY,
    order_id INTEGER,
    old_status VARCHAR(20),
    new_status VARCHAR(20),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 订单状态变更验证
CREATE OR REPLACE FUNCTION validate_order_status()
RETURNS TRIGGER
AS $$
BEGIN
    -- 只允许特定的状态转换
    IF TG_OP = 'INSERT' THEN
        IF NEW.status NOT IN ('pending', 'draft') THEN
            RAISE EXCEPTION '新订单状态只能是pending或draft';
        END IF;
        
    ELSIF TG_OP = 'UPDATE' THEN
        -- 定义允许的状态转换
        IF OLD.status = 'pending' AND NEW.status NOT IN ('confirmed', 'cancelled') THEN
            RAISE EXCEPTION 'pending状态只能转换为confirmed或cancelled';
        END IF;
        
        IF OLD.status = 'confirmed' AND NEW.status NOT IN ('shipped', 'cancelled') THEN
            RAISE EXCEPTION 'confirmed状态只能转换为shipped或cancelled';
        END IF;
        
        IF OLD.status = 'shipped' AND NEW.status NOT IN ('delivered', 'returned') THEN
            RAISE EXCEPTION 'shipped状态只能转换为delivered或returned';
        END IF;
        
        -- 已完成或已取消的订单不能修改状态
        IF OLD.status IN ('delivered', 'returned', 'cancelled') THEN
            RAISE EXCEPTION '已完成或已取消的订单不能修改状态';
        END IF;
        
        -- 记录状态变更历史
        IF OLD.status != NEW.status THEN
            INSERT INTO order_status_history (order_id, old_status, new_status)
            VALUES (NEW.order_id, OLD.status, NEW.status);
        END IF;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

软删除

-- 软删除触发器
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price NUMERIC(10, 2),
    is_deleted BOOLEAN DEFAULT FALSE,
    deleted_at TIMESTAMP
);

-- 创建实际数据表（不包含软删除字段）
CREATE TABLE products_data (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price NUMERIC(10, 2)
);

-- 创建软删除视图
CREATE VIEW products AS
SELECT id, name, price, FALSE AS is_deleted, NULL::TIMESTAMP AS deleted_at
FROM products_data;

-- INSTEAD OF触发器实现软删除
CREATE OR REPLACE FUNCTION soft_delete_product()
RETURNS TRIGGER
AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        -- 软删除：更新is_deleted标志
        -- 这里需要实际表来存储删除状态
        -- 简化示例：直接删除
        DELETE FROM products_data WHERE id = OLD.id;
        RETURN OLD;
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

管理触发器

查看触发器

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

-- 查看所有触发器
SELECT 
    trigger_name,
    event_manipulation AS operation,
    event_object_table AS table_name,
    action_timing AS timing,
    action_orientation AS level
FROM information_schema.triggers
WHERE trigger_schema = 'public';

-- 查看触发器详情
SELECT 
    tgname AS 触发器名称,
    tgrelid::regclass AS 表名,
    tgtype::TEXT AS 类型,
    tgenabled AS 状态,
    pg_get_triggerdef(oid) AS 定义
FROM pg_trigger
WHERE tgrelid = 'employees'::regclass
AND NOT tgisinternal;  -- 排除系统触发器

禁用和启用触发器

-- 禁用特定触发器
ALTER TABLE employees DISABLE TRIGGER trg_log_salary_change;

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

-- 启用特定触发器
ALTER TABLE employees ENABLE TRIGGER trg_log_salary_change;

-- 启用表上所有触发器
ALTER TABLE employees ENABLE TRIGGER ALL;

-- 启用/禁用复制触发器
ALTER TABLE employees ENABLE REPLICA TRIGGER trg_log_salary_change;
ALTER TABLE employees ENABLE ALWAYS TRIGGER trg_log_salary_change;

删除触发器

-- 删除触发器
DROP TRIGGER trg_log_salary_change ON employees;

-- 如果存在则删除
DROP TRIGGER IF EXISTS trg_log_salary_change ON employees;

触发器执行顺序

-- 同一表上的多个触发器按名称字母顺序执行
-- BEFORE触发器先于AFTER触发器

-- 查看触发器执行顺序
SELECT 
    tgname,
    tgtype,
    tgfoid::regprocedure AS function
FROM pg_trigger
WHERE tgrelid = 'employees'::regclass
AND NOT tgisinternal
ORDER BY tgname;

-- 可以通过命名控制执行顺序
-- trg_01_first_trigger
-- trg_02_second_trigger
-- trg_03_third_trigger

触发器最佳实践

性能考虑

-- 1. 避免在触发器中执行耗时操作
-- 不好的做法：在触发器中发送邮件
CREATE OR REPLACE FUNCTION bad_trigger()
RETURNS TRIGGER
AS $$
BEGIN
    -- 不要在触发器中执行网络操作
    -- SELECT send_email(...);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 好的做法：记录到队列表，由后台任务处理
CREATE TABLE notification_queue (
    id SERIAL PRIMARY KEY,
    payload JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed BOOLEAN DEFAULT FALSE
);

CREATE OR REPLACE FUNCTION queue_notification()
RETURNS TRIGGER
AS $$
BEGIN
    INSERT INTO notification_queue (payload)
    VALUES (to_jsonb(NEW));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. 使用条件减少触发器执行
CREATE TRIGGER trg_conditional
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (OLD.salary IS DISTINCT FROM NEW.salary)  -- 只在薪水变化时触发
EXECUTE FUNCTION log_salary_change();

调试触发器

-- 使用RAISE NOTICE调试
CREATE OR REPLACE FUNCTION debug_trigger()
RETURNS TRIGGER
AS $$
BEGIN
    RAISE NOTICE '触发器 % 在表 % 上执行', TG_NAME, TG_TABLE_NAME;
    RAISE NOTICE '操作类型: %', TG_OP;
    
    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        RAISE NOTICE 'NEW数据: %', NEW;
    END IF;
    
    IF TG_OP IN ('UPDATE', 'DELETE') THEN
        RAISE NOTICE 'OLD数据: %', OLD;
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

小结

本章介绍了PostgreSQL触发器的主要内容：

内容	说明
BEFORE触发器	操作前执行，可修改数据
AFTER触发器	操作后执行，用于审计日志
INSTEAD OF触发器	替代操作，用于视图
行级触发器	每行触发一次
语句级触发器	每语句触发一次
特殊变量	TG_OP, NEW, OLD等

触发器是数据库自动化的重要工具，下一章我们将学习事务处理。