Geek asks and answers

PostgreSQL trigger doesn't work - BEFORE or AFTER REMOVING

I just left MySQL in favor of PostgreSQL, and I have a question about triggers. This trigger is designed to update a field in the workflow table if a row has been 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 double:

  • If I use AFTER DELETE as above, the row will be deleted, but the update statement does not update the field in the workflow table.

  • If I use BEFORE DELETE, the process table will not perform the deletion at all and gives the error message "There is no unique identifier for this row."

Can anyone advise?

+5
source share
1 answer

Question 2:

Your trigger function ends:

RETURN NULL;

. :

, BEFORE, null, , ( , , INSERT/UPDATE/DELETE ).

:

RETURN OLD;

. :

DELETE, , nonnull, . , NEW null DELETE , . DELETE - return OLD.

.

1

, AFTER DELETE. , process_id workflow.

+14

More articles:


All Articles