In PostgreSQL, I have a table with a trigger defined which calls a defined PostgreSQL function. The trigger is BEFORE UPDATE. With the trigger defined, I am unable to make updates to the table in question. If I redefine the trigger as AFTER UPDATE everything works fine. Any ideas as to why? The table is similar to the following:

create table user (
id bigserial constraint pk_userprimary key,
username                 varchar,
hasreadinstructions      smallint,
hasstarted               smallint,
hasendeddataentry        smallint,
hasviewedresults         smallint,
email                    varchar,
firstname                varchar,
lastname                 varchar,
registrationdate         timestamp);

And the function is:

create function perform_notify() returns trigger
language plpgsql
as
$$
BEGIN
   PERFORM pg_notify('has_read_instructions', 'foo');
   RETURN NULL;
END;
$$;

The trigger is:

CREATE TRIGGER has_read_instructions BEFORE UPDATE
ON user FOR EACH ROW EXECUTE PROCEDURE
perform_notify();

If I define the trigger with BEFORE UPDATE, as shown, updates to the table do not work, such as:

update user set hasreadinstructions = 1 where id = 1;

The result is UPDATE 0 and the value is unchanged.

If I change the trigger to AFTER UPDATE, everything seems to be fine and my update to the table works with the result being UPDATE 1 with the value being changed.

In this case, I don't particularly care if I use BEFORE or AFTER, but I'm at a loss as to why; so any help in understanding this will be appreciated.

CodePudding user response：

To quote the documentation:

Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row).

In other words, by returning null, you're aborting the update operation. Instead, you can return the new row:

create function perform_notify() returns trigger
language plpgsql
as
$$
BEGIN
   PERFORM pg_notify('has_read_instructions', 'foo');
   RETURN NEW; -- Here!
END;
$$;