PostgreSQL教程

基本触发器函数语法

-- 创建基本的触发器函数
CREATE OR REPLACE FUNCTION trigger_function_name()
RETURNS TRIGGER AS $$
BEGIN
    -- 触发器逻辑
    RETURN NEW;  -- 或者 RETURN OLD; 或者 RETURN NULL;
END;
$$ LANGUAGE plpgsql;

触发器函数中的特殊变量：

• NEW：INSERT和UPDATE操作中的新行数据
• OLD：UPDATE和DELETE操作中的旧行数据
• TG_OP：触发触发器的操作类型（'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE'）
• TG_TABLE_NAME：触发触发器的表名
• TG_TABLE_SCHEMA：触发触发器的表模式名
• TG_WHEN：触发时机（'BEFORE', 'AFTER', 'INSTEAD OF'）
• TG_LEVEL：触发器级别（'ROW', 'STATEMENT'）

简单触发器函数示例

-- 创建记录用户插入时间的触发器函数
CREATE OR REPLACE FUNCTION set_user_insert_time()
RETURNS TRIGGER AS $$
BEGIN
    NEW.created_at = CURRENT_TIMESTAMP;
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建验证数据的触发器函数
CREATE OR REPLACE FUNCTION validate_user_data()
RETURNS TRIGGER AS $$
BEGIN
    -- 验证邮箱格式
    IF NEW.email !~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
        RAISE EXCEPTION '无效的邮箱格式';
    END IF;
    
    -- 验证用户名长度
    IF LENGTH(NEW.username) < 3 THEN
        RAISE EXCEPTION '用户名长度不能少于3个字符';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER语法

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

语法参数说明：

• name：触发器名称
• BEFORE/AFTER/INSTEAD OF：触发时机
• event：触发事件（INSERT、UPDATE、DELETE、TRUNCATE）
• table_name：触发器关联的表名
• FOR EACH ROW/STATEMENT：触发器级别
• WHEN (condition)：触发条件（可选）
• function_name：触发器函数名称

创建行级触发器

-- 创建行级BEFORE触发器
CREATE TRIGGER user_before_insert_trigger
    BEFORE INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION set_user_insert_time();

-- 创建行级AFTER触发器
CREATE TRIGGER user_after_insert_trigger
    AFTER INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION send_welcome_email();

-- 创建UPDATE行级触发器
CREATE TRIGGER user_update_trigger
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION set_user_update_time();

创建语句级触发器

-- 创建语句级触发器记录操作摘要
CREATE OR REPLACE FUNCTION log_statement_summary()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO operation_log (table_name, operation, timestamp, user_name)
    VALUES (TG_TABLE_NAME, TG_OP, CURRENT_TIMESTAMP, USER);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_statement_trigger
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH STATEMENT
    EXECUTE FUNCTION log_statement_summary();

创建多事件触发器

-- 创建处理多种操作的触发器
CREATE OR REPLACE FUNCTION handle_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    CASE TG_OP
        WHEN 'INSERT' THEN
            -- 处理插入操作
            NEW.created_at = CURRENT_TIMESTAMP;
            NEW.updated_at = CURRENT_TIMESTAMP;
            RETURN NEW;
        WHEN 'UPDATE' THEN
            -- 处理更新操作
            NEW.updated_at = CURRENT_TIMESTAMP;
            RETURN NEW;
        WHEN 'DELETE' THEN
            -- 处理删除操作
            INSERT INTO user_archive SELECT OLD.*;
            RETURN OLD;
        ELSE
            RETURN NULL;
    END CASE;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_multi_event_trigger
    BEFORE INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION handle_user_changes();

创建带条件的触发器

-- 创建带条件的触发器
CREATE TRIGGER salary_update_trigger
    BEFORE UPDATE ON employees
    FOR EACH ROW
    WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
    EXECUTE FUNCTION log_salary_change();

-- 复杂条件触发器
CREATE TRIGGER important_customer_trigger
    AFTER UPDATE ON customers
    FOR EACH ROW
    WHEN (NEW.total_purchases > 10000 OR NEW.customer_level = 'VIP')
    EXECUTE FUNCTION notify_important_customer_update();

审计日志触发器

-- 创建审计日志表
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(50),
    operation VARCHAR(10),
    row_data JSONB,
    changed_data JSONB,
    user_name VARCHAR(50),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建审计触发器函数
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO audit_log (table_name, operation, row_data, user_name)
        VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), USER);
        RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO audit_log (table_name, operation, row_data, user_name)
        VALUES (TG_TABLE_NAME, TG_OP, row_to_json(NEW), USER);
        RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit_log (table_name, operation, row_data, changed_data, user_name)
        VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), USER);
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 为多个表创建审计触发器
CREATE TRIGGER users_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger_function();

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

数据同步触发器

-- 创建数据同步触发器函数
CREATE OR REPLACE FUNCTION sync_user_data()
RETURNS TRIGGER AS $$
BEGIN
    -- 同步到历史表
    INSERT INTO user_history (
        user_id, username, email, created_at, updated_at, sync_time
    ) VALUES (
        NEW.id, NEW.username, NEW.email, NEW.created_at, NEW.updated_at, CURRENT_TIMESTAMP
    ) ON CONFLICT (user_id) DO UPDATE SET
        username = EXCLUDED.username,
        email = EXCLUDED.email,
        updated_at = EXCLUDED.updated_at,
        sync_time = EXCLUDED.sync_time;
    
    -- 同步到统计表
    INSERT INTO user_statistics (user_id, registration_date)
    VALUES (NEW.id, NEW.created_at)
    ON CONFLICT (user_id) DO NOTHING;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_sync_trigger
    AFTER INSERT OR UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION sync_user_data();

业务规则触发器

-- 创建库存检查触发器函数
CREATE OR REPLACE FUNCTION check_inventory_before_order()
RETURNS TRIGGER AS $$
DECLARE
    available_stock INTEGER;
BEGIN
    -- 检查产品库存
    SELECT stock_quantity INTO available_stock
    FROM products
    WHERE id = NEW.product_id;
    
    -- 如果库存不足，抛出异常
    IF available_stock < NEW.quantity THEN
        RAISE EXCEPTION '产品ID % 库存不足，当前库存：% ，需求数量：%', 
            NEW.product_id, available_stock, NEW.quantity;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_inventory_check_trigger
    BEFORE INSERT ON order_items
    FOR EACH ROW
    EXECUTE FUNCTION check_inventory_before_order();

-- 创建订单总价计算触发器
CREATE OR REPLACE FUNCTION calculate_order_total()
RETURNS TRIGGER AS $$
BEGIN
    -- 计算订单总价
    SELECT SUM(quantity * price) INTO NEW.total_amount
    FROM order_items
    WHERE order_id = NEW.id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

命名规范

-- 推荐的触发器命名规范
-- {表名}_{操作}_{触发时机}_{功能}_trigger
CREATE TRIGGER users_insert_before_validation_trigger
    BEFORE INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION validate_user_data();

-- {表名}_{功能}_trigger
CREATE TRIGGER orders_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION audit_order_changes();

错误处理

-- 带错误处理的触发器函数
CREATE OR REPLACE FUNCTION safe_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    BEGIN
        -- 主要逻辑
        INSERT INTO log_table (message, timestamp)
        VALUES ('Trigger executed for ' || TG_TABLE_NAME, CURRENT_TIMESTAMP);
    EXCEPTION
        WHEN OTHERS THEN
            -- 记录错误但不中断主操作
            INSERT INTO error_log (error_message, table_name, operation, timestamp)
            VALUES (SQLERRM, TG_TABLE_NAME, TG_OP, CURRENT_TIMESTAMP);
            -- 根据需要决定是否继续执行
            -- RETURN NULL; -- 如果是AFTER触发器，可以返回NULL
    END;
    
    RETURN NEW; -- BEFORE触发器返回NEW，AFTER触发器可以返回NULL
END;
$$ LANGUAGE plpgsql;