PostgreSQL trigger not working - neither BEFORE nor AFTER DELETE

Question

This Postgres trigger is designed to update a field in the workflow table if a row is deleted in the processes table:

CREATE OR REPLACE FUNCTION fn_process_delete() RETURNS TRIGGER AS $$
BEGIN
    UPDATE workflow SET deleted_process_name = OLD.process_name
    WHERE process_id = OLD.process_id;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS process_delete ON processes;
CREATE TRIGGER process_delete
AFTER DELETE ON processes
FOR EACH ROW 
EXECUTE PROCEDURE fn_process_delete();

My question is two-fold:

If I use AFTER DELETE as above, the row will delete, but the update statement does not update the field in the 'workflow' table.
If I use BEFORE DELETE, the processes table will not perform the delete at all and delivers an error saying "No unique identifier for this row".

Can anyone advise?

Erwin Brandstetter · Accepted Answer · 2024-07-29 21:05:23Z

23

Question 2

Your trigger function ends with:

RETURN NULL;

That skips the execution of the triggering event. The manual:

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).

To proceed with the deletion of the row, replace that with:

RETURN OLD;

Here is why:

In the case of a before-trigger on DELETE, the returned value has no direct effect, but it has to be nonnull to allow the trigger action to proceed. Note that NEW is null in DELETE triggers, so returning that is usually not sensible. The usual idiom in DELETE triggers is to return OLD.

Bold emphasis mine.

Question 1

I see no reason why your trigger and trigger function should not work as AFTER DELETE. Obviously, a row with a matching process_id has to exist in table workflow.

edited Jul 29, 2024 at 21:05

answered May 21, 2012 at 15:16

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kinderplatonic Over a year ago

Great - thanks! After MySQL I wasn't really familiar with trigger RETURN behaviour.

Collectives™ on Stack Overflow

PostgreSQL trigger not working - neither BEFORE nor AFTER DELETE

1 Answer 1

Question 2

Question 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Question 2

Question 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related