如何通过触发器实现news/requirements/conditions三表单语句批量插入

没问题，咱们来搞定这个需求：你想用单条SQL语句同时往news、requirements、conditions三张表插入数据，还要通过news_mechanic中间表完成关联，用触发器来实现对吧？下面是针对PostgreSQL的完整方案（因为你用到了SERIAL，这是PG的专属特性）：

1. 先补全并修正所有表结构 #

你给出的表结构有一些缺失（比如conditions和requirements没定义主键、部分字段类型不明确，关键的news_mechanic中间表也没给出），我先把它们补成符合PG规范的结构：

-- 基础表news：保持你原来的定义，加了默认日期更实用
CREATE TABLE news (
    id_news SERIAL PRIMARY KEY,
    text_news VARCHAR NULL,
    date_created DATE NULL DEFAULT CURRENT_DATE
);

-- conditions表：补全主键和明确字段类型
CREATE TABLE conditions (
    id_conditions SERIAL PRIMARY KEY,
    gender_men BOOLEAN NULL,
    gender_women BOOLEAN NULL,
    age_from INTEGER NULL,
    age_to INTEGER NULL,
    etc VARCHAR NULL -- 这里假设etc是字符串类型，你可以根据实际业务调整
);

-- requirements表：补全主键、修正拼写（cunsultation改成consultation）并明确类型
CREATE TABLE requirements (
    id_requirements SERIAL PRIMARY KEY,
    name VARCHAR NULL,
    mechanic_test BOOLEAN NULL,
    mechanic_consultation BOOLEAN NULL,
    etc VARCHAR NULL -- 同理，按需调整类型
);

-- 中间关联表news_mechanic：用来关联三张主表，设复合主键避免重复关联
CREATE TABLE news_mechanic (
    id_news INTEGER REFERENCES news(id_news) ON DELETE CASCADE,
    id_requirements INTEGER REFERENCES requirements(id_requirements) ON DELETE CASCADE,
    id_conditions INTEGER REFERENCES conditions(id_conditions) ON DELETE CASCADE,
    PRIMARY KEY (id_news, id_requirements, id_conditions)
);

2. 创建整合所有字段的视图 #

直接对三张表写INSERT是行不通的，所以我们先建一个视图，把三张表需要插入的字段全部整合在一起。这样你后续只需要对这个视图执行单条INSERT即可：

CREATE VIEW news_with_details AS
SELECT 
    n.id_news,
    n.text_news,
    n.date_created,
    c.gender_men,
    c.gender_women,
    c.age_from,
    c.age_to,
    c.etc AS conditions_etc,
    r.name,
    r.mechanic_test,
    r.mechanic_consultation,
    r.etc AS requirements_etc
FROM news n
LEFT JOIN news_mechanic nm ON n.id_news = nm.id_news
LEFT JOIN conditions c ON nm.id_conditions = c.id_conditions
LEFT JOIN requirements r ON nm.id_requirements = r.id_requirements;

3. 给视图写INSTEAD OF INSERT触发器 #

这个触发器的作用是：当你往视图插数据时，它会拦截这个操作，转而分别往三张主表插入数据，最后在中间表建立关联。

先写触发器函数：

CREATE OR REPLACE FUNCTION insert_news_with_details()
RETURNS TRIGGER AS $$
DECLARE
    new_news_id INTEGER;
    new_conditions_id INTEGER;
    new_requirements_id INTEGER;
BEGIN
    -- 第一步：插入news表，获取自动生成的id_news
    INSERT INTO news (text_news, date_created)
    VALUES (NEW.text_news, COALESCE(NEW.date_created, CURRENT_DATE))
    RETURNING id_news INTO new_news_id;

    -- 第二步：插入conditions表，获取自动生成的id_conditions
    INSERT INTO conditions (gender_men, gender_women, age_from, age_to, etc)
    VALUES (NEW.gender_men, NEW.gender_women, NEW.age_from, NEW.age_to, NEW.conditions_etc)
    RETURNING id_conditions INTO new_conditions_id;

    -- 第三步：插入requirements表，获取自动生成的id_requirements
    INSERT INTO requirements (name, mechanic_test, mechanic_consultation, etc)
    VALUES (NEW.name, NEW.mechanic_test, NEW.mechanic_consultation, NEW.requirements_etc)
    RETURNING id_requirements INTO new_requirements_id;

    -- 第四步：插入中间表，把三个表的记录关联起来
    INSERT INTO news_mechanic (id_news, id_requirements, id_conditions)
    VALUES (new_news_id, new_requirements_id, new_conditions_id);

    -- 返回插入后的视图行（可选，用来确认插入结果）
    SELECT 
        new_news_id,
        NEW.text_news,
        COALESCE(NEW.date_created, CURRENT_DATE),
        NEW.gender_men,
        NEW.gender_women,
        NEW.age_from,
        NEW.age_to,
        NEW.conditions_etc,
        NEW.name,
        NEW.mechanic_test,
        NEW.mechanic_consultation,
        NEW.requirements_etc
    INTO NEW;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

然后把触发器绑定到视图上：

CREATE TRIGGER trigger_insert_news_with_details
INSTEAD OF INSERT ON news_with_details
FOR EACH ROW EXECUTE FUNCTION insert_news_with_details();

4. 测试单条SQL插入 #

现在你只需要执行下面这一条INSERT语句，就能自动完成三张主表的插入和中间表的关联：

INSERT INTO news_with_details (text_news, date_created, gender_men, gender_women, age_from, age_to, conditions_etc, name, mechanic_test, mechanic_consultation, requirements_etc)
VALUES (
    '2024年夏季招聘公告',
    '2024-05-20',
    TRUE,
    TRUE,
    22,
    35,
    '需本科及以上学历',
    '招聘资格要求',
    TRUE,
    FALSE,
    '需提供学历证明和工作经历'
);

执行完后，你可以分别查询news、conditions、requirements和news_mechanic表，就能看到所有关联的数据都已经正确插入了。

额外说明 #

• 如果你的业务允许requirements或conditions被多条news复用（比如多个新闻用同一个条件），那需要修改触发器逻辑：先判断是否存在相同的记录，存在的话直接用已有ID，不存在再插入新记录。
• 上面的代码是PostgreSQL专属的，如果你用的是MySQL等其他数据库，触发器语法会有差异，需要对应调整。
• COALESCE(NEW.date_created, CURRENT_DATE)是为了在不传入date_created时自动用当前日期，你可以根据需求去掉或者修改默认值。

内容的提问来源于stack exchange，提问作者Павел Филимонов