postgres - 在事务提交之前触发

Question

我想知道是否可以间接地在事务即将提交之前执行触发器？在此触发器中，我将进行一致性检查并在需要时回滚事务。

例如，我有三个表：

users (id, name)
groups (id, name)
user_in_group (user_id, group_id)

我想创建一个触发器来验证用户始终是组的一部分。不允许孤儿用户。每次发生对 users 的插入时，此触发器都会验证是否也发生了对 user_in_group 的相应插入。如果不是，事务将不会提交。

使用简单的基于行或基于语句的触发器无法完成此操作，因为上述场景需要两个单独的语句。

相反，当从 user_in_group 发生删除时，可以通过基于行的触发器轻松完成。

Answer 1

您是否查看了 CREATE CONSTRAINT TRIGGER，其中 DEFERRABLE (INITIALLY DEFERRED) 选项？

Answer 2

正确的方法难道不是在数据库中建立约束吗？这似乎正是约束的用途。添加一个外键约束和一个非空约束，看起来你应该开始做生意了。

现在针对对称约束进行了修改：

drop table foousers cascade;
drop table foogroups cascade;
drop table foousergrps cascade;
create table foousers (id int primary key, name text);
create table foogroups  (id int primary key, name text);
create table foousergrps (user_id int unique references foousers not null, group_id int unique references foogroups not null);
alter table foogroups add foreign key (id) references foousergrps (group_id) deferrable initially deferred;
alter table foousers add foreign key (id) references foousergrps (user_id) deferrable initially deferred;
begin;
insert into foousers values (0, 'root');
insert into foousers values (1, 'daemon');
insert into foogroups values (0, 'wheel');
insert into foogroups values (1, 'daemon');
insert into foousergrps values (0,0);
insert into foousergrps values (1,1);
commit;

禁止：

insert into foousers values (2, 'bad');
insert into foousergrps values (2,2);

(non-deferable, boo) 检查函数示例：

create table foousergrps (user_id int unique references foousers not null, group_id int not null);
create function fooorphangroupcheck(int) returns boolean as $
declare
  gid alias for $1;
begin
  perform 1 from foousergrps where group_id = gid limit 1;
  if NOT FOUND then return false;
  end if;
  return true;
end;
$
LANGUAGE 'plpgsql';
alter table foogroups add check (fooorphangroupcheck(id));

Answer 3

查看文档，似乎没有这样的触发选项...因此实现“无孤儿用户”规则的一种方法是不允许直接插入 users 和 user_in_group代码>表。而是创建一个带有 更新规则，将数据插入到正确的表中。

或者只允许通过存储过程插入新用户，该存储过程将所有必需的数据作为输入，因此不允许没有组的用户。

顺便说一句，为什么你有单独的用户和组关系表？为什么不将 group_id 字段添加到具有 FK / NOT NULL 约束的 users 表中？

Answer 4

来自文档。 。 。

触发器可以定义为在任何 INSERT 之前或之后执行，
UPDATE 或 DELETE 操作，每个修改行一次，或者每个修改行一次
SQL语句。

Answer 5

您可以使用 sql WITH 运算符，如下所示：

WITH insert_user AS (
  INSERT INTO users(name) VALUES ('bla-bla-user') RETURNING id
)
INSERT INTO user_in_group(user_id, group_id) 
  SELECT id, 999 FROM insert_user UNION
  SELECT id, 888 FROM insert_user;

SELECT groups (id, name) user_in_group (user_id, group_id)