Postgres - trigger before transaction

Question

I was wondering if it is possible to indirectly trigger a trigger executed just before a transaction? In this trigger, I will perform consistency checks and roll back the transaction, if necessary.

For example, I have three tables:

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

I would like to create a trigger that checks that the user is always part of a group. The use of orphans is not permitted. Each time an insert into users occurs, this trigger will check that an offset insert is also inserted into user_in_group. If not, the transaction will not be completed.

This cannot be done with a simple trigger based on a string or an operator, since two separate statements are required in the above scenario.

Another way, when a deletion from user_in_group occurs, can be easily done using a row-based trigger.

Answer 1

Have you viewed CREATE CONSTRAINT TRIGGER with the DEFERRABLE (INITIALLY DEFERRED) option?

Answer 2

Look at the document, there seems to be no such trigger option ... therefore, one way to achieve the "no orphaned users" rule would be to prevent direct insertion into the users and user_in_group . Instead, create a view (which joins these tables, i.e. user_id, user_name, group_id ) with an update rule that inserts data into the right tables.

Or allow only the insertion of new users through a stored procedure that accepts all the required data as inpud and thus does not allow users to group the group.

By the way, why do you have a separate table for user and group relationships? Why not add the group_id field to the users table with an FK / NOT NULL ?

Answer 3

Is the method wrong to really set limits on the database? This is similar to what constraints are for. Add a foreign key constraint and not null, and it looks like you should be in business.

Now revised for symmetric constraints:

 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; 

Forbidden:

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

An example of a validation function (non-deferrable, 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 4

From the documents .,.

Triggers can be defined to execute before or after any INSERT, UPDATE, or DELETE, either once for a modified row, or once per SQL.

Answer 5

You can use the sql WITH statement, for example:

 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)