如何编写新增部门时自动生成带固定表新schema的trigger？

以下实现基于PostgreSQL数据库，核心逻辑是在部门信息表上绑定行级后插入触发器，动态执行Schema和表的创建操作：

1. 前置依赖：部门基础表 #

首先确认存储部门注册信息的基础表已存在，示例结构如下：

CREATE TABLE IF NOT EXISTS public.department (
    dept_id SERIAL PRIMARY KEY,
    dept_code VARCHAR(32) NOT NULL UNIQUE, -- 部门唯一编码，用于生成Schema名避免冲突
    dept_name VARCHAR(64) NOT NULL,
    register_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

注意：不要使用中文、特殊字符作为部门编码，避免Schema命名非法

2. 编写触发器执行函数 #

函数负责接收新插入的部门数据，动态创建Schema和带约束的固定表：

CREATE OR REPLACE FUNCTION public.auto_create_dept_schema()
RETURNS TRIGGER AS $$
DECLARE
    schema_name VARCHAR := 'dept_' || NEW.dept_code; -- Schema命名规则：dept_+部门唯一编码
BEGIN
    -- 1. 创建部门专属Schema，不存在时才创建避免报错
    EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I', schema_name);

    -- 2. 新建Schema下第一张固定表：部门员工表，带非空、唯一、正则校验等约束
    EXECUTE format('
        CREATE TABLE IF NOT EXISTS %I.dept_user (
            user_id SERIAL PRIMARY KEY,
            username VARCHAR(32) NOT NULL UNIQUE,
            real_name VARCHAR(32) NOT NULL,
            phone VARCHAR(11) CHECK (phone ~ ''^1[3-9]\d{9}$''),
            email VARCHAR(64) CHECK (email ~ ''^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$''),
            create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            is_valid BOOLEAN NOT NULL DEFAULT TRUE
        )', schema_name);

    -- 3. 新建Schema下第二张固定表：部门项目表，带非空、唯一、外键、数值校验等约束
    EXECUTE format('
        CREATE TABLE IF NOT EXISTS %I.dept_project (
            project_id SERIAL PRIMARY KEY,
            project_code VARCHAR(32) NOT NULL UNIQUE,
            project_name VARCHAR(64) NOT NULL,
            principal_id INT NOT NULL REFERENCES %I.dept_user(user_id), -- 外键关联同Schema下的员工表
            start_date DATE NOT NULL,
            end_date DATE CHECK (end_date > start_date),
            budget NUMERIC(12,2) CHECK (budget >= 0),
            create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        )', schema_name, schema_name);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

说明：SECURITY DEFINER参数确保触发器执行时使用函数创建者的高权限，避免普通账号插入部门数据时无创建Schema/表的权限；使用%I占位符做标识符转义，规避SQL注入风险

3. 绑定触发器到部门表 #

CREATE TRIGGER trg_after_dept_insert
AFTER INSERT ON public.department
FOR EACH ROW EXECUTE FUNCTION public.auto_create_dept_schema();

4. 验证效果 #

-- 插入新注册的部门数据
INSERT INTO public.department(dept_code, dept_name) VALUES ('sales001', '华东销售一部');

-- 校验Schema是否生成
SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'dept_sales001';
-- 校验两张固定表是否创建成功
SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = 'dept_sales001';

注意事项 #

• 可根据实际业务需求调整两张固定表的字段、约束规则
• 生产环境使用前请严格管控函数创建者权限，避免SECURITY DEFINER带来的权限溢出风险
• MySQL等不支持触发器内执行DDL的数据库，需要改用注册完成后调用存储过程、监听binlog触发后续操作的方案实现

内容的提问来源于stack exchange，提问作者sandesh Jadhav