将插入的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 插入 FOR 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屋！