PostgreSQL trigger not working - neither BEFORE nor AFTER DELETE

Question

I have just left MySQL behind in favor of PostgreSQL, and I have a question regarding triggers. This 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:

1. If I use AFTER DELETE as above, the row will delete, but the update statement does not update the field in the 'workflow' table.

2. 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?

Answer 1

Question 2:

Your trigger function ends with:

RETURN NULL;

With that you skip the execution of the triggering event. Per documentation on trigger procedures:

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

You need to replace that with:

RETURN OLD;

for the system to proceed with the deletion of the row. 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. It goes without saying that a row with a matching process_id has to exist in table workflow.