PostgreSQL trigger not working - neither BEFORE nor AFTER DELETE
plpgsql, postgresql, triggers
Solution
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`.
Problem
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: 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?