PostgreSQL教程

触发器激活示例

-- 假设已创建以下触发器
CREATE TRIGGER user_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION audit_user_changes();

-- 当执行以下操作时，触发器会自动激活
-- 插入操作激活触发器
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

-- 更新操作激活触发器
UPDATE users SET email = 'john_new@example.com' WHERE username = 'john_doe';

-- 删除操作激活触发器
DELETE FROM users WHERE username = 'john_doe';

触发器激活的关键点：

• 触发器在指定事件发生时自动执行
• 触发器的执行顺序遵循创建顺序
• BEFORE触发器在事件发生前执行
• AFTER触发器在事件发生后执行
• INSTEAD OF触发器替代原始操作执行

访问NEW和OLD数据

-- 创建演示触发器函数
CREATE OR REPLACE FUNCTION demonstrate_trigger_data()
RETURNS TRIGGER AS $$
BEGIN
    -- 访问NEW数据（INSERT和UPDATE操作）
    IF TG_OP = 'INSERT' THEN
        RAISE NOTICE '插入新行: ID=%, 用户名=%', NEW.id, NEW.username;
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        -- 访问NEW和OLD数据（UPDATE操作）
        RAISE NOTICE '更新行: ID=%, 旧用户名=%, 新用户名=%', 
            NEW.id, OLD.username, NEW.username;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        -- 访问OLD数据（DELETE操作）
        RAISE NOTICE '删除行: ID=%, 用户名=%', OLD.id, OLD.username;
        RETURN OLD;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER demo_trigger
    BEFORE INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION demonstrate_trigger_data();

-- 测试触发器
INSERT INTO users (username, email) VALUES ('test_user', 'test@example.com');
UPDATE users SET username = 'updated_user' WHERE username = 'test_user';
DELETE FROM users WHERE username = 'updated_user';

条件触发器的使用

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

-- 只有当薪资发生变化时，触发器才会激活
UPDATE employees SET salary = 5000 WHERE id = 1;  -- 薪资改变，触发器激活
UPDATE employees SET department = 'IT' WHERE id = 1;  -- 薪资未变，触发器不激活

启用和禁用触发器

-- 禁用特定触发器
ALTER TABLE users DISABLE TRIGGER user_audit_trigger;

-- 启用特定触发器
ALTER TABLE users ENABLE TRIGGER user_audit_trigger;

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

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

-- 禁用表上的用户定义触发器（保留系统触发器）
ALTER TABLE users DISABLE TRIGGER USER;

-- 启用表上的用户定义触发器
ALTER TABLE users ENABLE TRIGGER USER;

触发器状态管理

-- 查看触发器状态
SELECT 
    tgname AS trigger_name,
    relname AS table_name,
    tgenabled AS trigger_status
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
WHERE NOT tgisinternal
ORDER BY relname, tgname;

-- 根据状态执行不同操作
DO $$
DECLARE
    trigger_record RECORD;
BEGIN
    FOR trigger_record IN 
        SELECT tgname, tgenabled
        FROM pg_trigger t
        JOIN pg_class c ON t.tgrelid = c.oid
        WHERE c.relname = 'users' AND NOT tgisinternal
    LOOP
        IF trigger_record.tgenabled = 'O' THEN
            RAISE NOTICE '触发器 % 已启用', trigger_record.tgname;
        ELSIF trigger_record.tgenabled = 'D' THEN
            RAISE NOTICE '触发器 % 已禁用', trigger_record.tgname;
        END IF;
    END LOOP;
END;
$$;

避免在触发器中执行耗时操作

-- 不好的做法：在触发器中执行复杂查询
CREATE OR REPLACE FUNCTION bad_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- 在触发器中执行复杂查询（不推荐）
    IF (SELECT COUNT(*) FROM large_table WHERE condition = NEW.value) > 100 THEN
        INSERT INTO log_table VALUES ('Complex condition met');
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 好的做法：简化触发器逻辑
CREATE OR REPLACE FUNCTION good_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- 只执行简单的操作
    INSERT INTO simple_log (table_name, operation, timestamp)
    VALUES (TG_TABLE_NAME, TG_OP, CURRENT_TIMESTAMP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

使用语句级触发器优化性能

-- 对于批量操作，使用语句级触发器
CREATE OR REPLACE FUNCTION batch_operation_summary()
RETURNS TRIGGER AS $$
BEGIN
    -- 记录批量操作的摘要信息
    INSERT INTO batch_log (table_name, operation, row_count, timestamp)
    VALUES (TG_TABLE_NAME, TG_OP, (SELECT COUNT(*) FROM inserted_rows), CURRENT_TIMESTAMP);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER batch_users_trigger
    AFTER INSERT ON users
    FOR EACH STATEMENT  -- 语句级触发器
    EXECUTE FUNCTION batch_operation_summary();

-- 批量插入时，触发器只执行一次
INSERT INTO users (username, email) 
SELECT 'user' || generate_series, 'user' || generate_series || '@example.com'
FROM generate_series(1, 1000);

异步处理触发器操作

-- 创建消息队列表
CREATE TABLE trigger_queue (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(50),
    operation VARCHAR(10),
    row_data JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed BOOLEAN DEFAULT FALSE
);

-- 创建简单的触发器函数
CREATE OR REPLACE FUNCTION queue_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- 将操作放入队列，而不是立即处理
    INSERT INTO trigger_queue (table_name, operation, row_data)
    VALUES (TG_TABLE_NAME, TG_OP, row_to_json(COALESCE(NEW, OLD)));
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- 创建处理队列的函数（可以由后台进程定期执行）
CREATE OR REPLACE FUNCTION process_trigger_queue()
RETURNS VOID AS $$
DECLARE
    queue_record RECORD;
BEGIN
    FOR queue_record IN 
        SELECT * FROM trigger_queue WHERE processed = FALSE
        ORDER BY created_at
        LIMIT 100  -- 每次处理100条记录
    LOOP
        -- 执行实际的触发器逻辑
        PERFORM process_queue_item(queue_record);
        
        -- 标记为已处理
        UPDATE trigger_queue 
        SET processed = TRUE 
        WHERE id = queue_record.id;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

触发器调试技巧

-- 使用RAISE NOTICE进行调试
CREATE OR REPLACE FUNCTION debug_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE '触发器激活: 表=%, 操作=%, 时间=%', 
        TG_TABLE_NAME, TG_OP, CURRENT_TIMESTAMP;
    
    IF TG_OP = 'INSERT' THEN
        RAISE NOTICE '插入数据: ID=%, 用户名=%', NEW.id, NEW.username;
    ELSIF TG_OP = 'UPDATE' THEN
        RAISE NOTICE '更新数据: ID=%, 旧用户名=%, 新用户名=%', 
            NEW.id, OLD.username, NEW.username;
    ELSIF TG_OP = 'DELETE' THEN
        RAISE NOTICE '删除数据: ID=%, 用户名=%', OLD.id, OLD.username;
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

触发器错误处理

-- 带错误处理的触发器函数
CREATE OR REPLACE FUNCTION safe_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    BEGIN
        -- 主要逻辑
        INSERT INTO audit_log (table_name, operation, data, timestamp)
        VALUES (TG_TABLE_NAME, TG_OP, row_to_json(COALESCE(NEW, OLD)), 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);
            
            -- 根据触发器类型决定是否继续
            IF TG_WHEN = 'BEFORE' THEN
                -- BEFORE触发器中可以选择跳过操作
                -- RETURN NULL; -- 跳过操作
                RETURN COALESCE(NEW, OLD); -- 继续操作
            ELSE
                -- AFTER触发器中的错误不应影响主操作
                RETURN COALESCE(NEW, OLD);
            END IF;
    END;
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

数据审计和日志记录

-- 创建完整的审计系统
CREATE TABLE data_audit (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(50),
    operation VARCHAR(10),
    old_data JSONB,
    new_data JSONB,
    user_name VARCHAR(50),
    client_addr INET,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO data_audit (table_name, operation, old_data, user_name, client_addr)
        VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), USER, inet_client_addr());
        RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO data_audit (table_name, operation, new_data, user_name, client_addr)
        VALUES (TG_TABLE_NAME, TG_OP, row_to_json(NEW), USER, inet_client_addr());
        RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO data_audit (table_name, operation, old_data, new_data, user_name, client_addr)
        VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), USER, inet_client_addr());
        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_data_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'INSERT') THEN
        -- 同步到历史表
        INSERT INTO users_history SELECT NEW.*;
        
        -- 同步到统计表
        INSERT INTO user_statistics (user_id, registration_date, total_logins)
        VALUES (NEW.id, NEW.created_at, 0)
        ON CONFLICT (user_id) DO NOTHING;
        
        RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        -- 更新历史表
        UPDATE users_history 
        SET username = NEW.username, 
            email = NEW.email, 
            updated_at = NEW.updated_at
        WHERE user_id = NEW.id;
        
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        -- 从统计表中删除
        DELETE FROM user_statistics WHERE user_id = OLD.id;
        
        RETURN OLD;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

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