Postgresql – Trigger for removing group with no users left in

postgresqlpostgresql-9.1trigger

I have three tables: USERS, GROUPS, and GROUP_MEMBERSHIP

CREATE TABLE USERS (
ID BIGSERIAL NOT NULL,
NICKNAME VARCHAR(20) NOT NULL constraint USER_EXISTS UNIQUE,
PRIMARY KEY (ID)
);

CREATE TABLE GROUPS (
ID SERIAL NOT NULL,
NAME VARCHAR(20) NOT NULL constraint GROUP_EXISTS UNIQUE,
PRIMARY KEY (ID)
);

CREATE TABLE GROUP_MEMBERSHIP (
ID SERIAL NOT NULL,
ID_USER BIGINT NOT NULL constraint USER_IN_GROUP UNIQUE,
ID_GROUP INT NOT NULL,
PRIMARY KEY (ID)
);

Now I am trying to create a trigger, which would fire after deleting a user from a group. If the group has no users left, then the group is deleted:

CREATE OR REPLACE FUNCTION DELETE_EMPTY_GROUPS()
RETURNS TRIGGER AS
$$
BEGIN
        IF NOT EXISTS(SELECT * FROM GROUP_MEMBERSHIP WHERE ID_GROUP = OLD.ID_GROUP)
        THEN
               SELECT DELETE_GROUP(OLD.ID_GROUP);
        END IF;
        RETURN NEW;
END
$$
LANGUAGE 'plpgsql';


DROP TRIGGER TR1 ON GROUP_MEMBERSHIP;
CREATE TRIGGER TR1
       BEFORE DELETE
       ON GROUP_MEMBERSHIP
       EXECUTE PROCEDURE DELETE_EMPTY_GROUPS();

Unfortunately, it does not work. I get the following error:

ERROR: record "old" is not assigned yet DETAIL: The tuple structure
of a not-yet-assigned record is indeterminate.

I tried BEFORE and AFTER, both resulted in the same error. What is the correct way of doing this?

Best Answer

If you don't specify FOR EACH ROW in your CREATE TRIGGER statement, it will default to FOR EACH STATEMENT. In this case, the OLD and NEW records will never be assigned - in the end, which row should they refer to, if you change, for example, a hundred of them?

So, create your trigger as follows:

CREATE TRIGGER TR1
       AFTER DELETE
       ON GROUP_MEMBERSHIP
       FOR EACH ROW
       EXECUTE PROCEDURE DELETE_EMPTY_GROUPS();

Using the idea from @Darwin von Corax, you could also use a FOR EACH STATEMENT trigger to remove all groups that are now empty:

CREATE OR REPLACE FUNCTION DELETE_EMPTY_GROUPS()
RETURNS TRIGGER AS
$$
BEGIN
    PERFORM DELETE_GROUP(g.ID)
    FROM GROUPS AS g
    WHERE NOT EXISTS (
        SELECT 1
        FROM GROUP_MEMBERSHIP AS gm
        WHERE gm.ID_GROUP = g.ID
    );
    RETURN NULL;
END
$$
LANGUAGE 'plpgsql';


CREATE TRIGGER TR1
   AFTER DELETE
   ON GROUP_MEMBERSHIP
   FOR EACH STATEMENT
   EXECUTE PROCEDURE DELETE_EMPTY_GROUPS();

Notes:

  • I believe the trigger should be defined as AFTER DELETE in both cases.
  • I also believe that your function throws an error (ERROR: query has no destination for result data) - use PERFORM instead of SELECT.
  • If you delete many rows (group memberships) at a time, the FOR EACH STATEMENT trigger might be more efficient, as it fires only once.
Related Question