Postgres -事务提交前触发

Question

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

例如，我有三个表:

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

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

这不能使用简单的基于行或语句的触发器来完成，因为上面的场景需要两个单独的语句。

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

Answer 1

您是否查看了带有DEFERRABLE (INITIALLY DEFERRED)选项的CREATE CONSTRAINT TRIGGER ?

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- deferred, boo) check函数示例:

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表中。相反，创建一个视图(它结合了这些表，即user_id, user_name, group_id)，并使用更新规则将数据插入到正确的表中。

或者只允许通过存储过程插入新用户，存储过程接受所有需要的数据作为输入，因此不允许无组用户。

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

Answer 4

From the docs .

触发器可以定义为在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)