将插入的 id 插入另一个表 [英] Insert inserted id to another table

问题描述

场景如下:

create table a (
 id serial primary key,
 val text
);

create table b (
 id serial primary key,
 a_id integer references a(id)
);

create rule a_inserted as on insert to a do also insert into b (a_id) values (new.id);

我正在尝试在 b 中创建一条记录，在插入 a 表时引用 a.但我得到的是 new.id 为空，因为它是从序列自动生成的.我还尝试了触发器 AFTER insert FOR EACH ROW，但结果是一样的.有什么办法可以解决这个问题吗?

I'm trying to create a record in b referencing to a on insertion to a table. But what I get is that new.id is null, as it's automatically generated from a sequence. I also tried a trigger AFTER insert FOR EACH ROW, but result was the same. Any way to work this out?

推荐答案

避免规则，因为它们会回来咬你.

Avoid rules, as they'll come back to bite you.

在为每一行运行的表 a 上使用 after 触发器.它应该看起来像这样(未经测试):

Use an after trigger on table a that runs for each row. It should look something like this (untested):

create function a_ins() returns trigger as $$
begin
  insert into b (a_id) values (new.id);
  return null;
end;
$$ language plpgsql;

create trigger a_ins after insert on a
for each row execute procedure a_ins();

这篇关于将插入的 id 插入另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！