CODEWITHSUNDEEP
sqlpostgresqldatabase-trigger
Bartek Szczypien
Bartek Szczypien

Reputation: 343

updating object inside BEFORE DELETE trigger has no effect

I need to clean up a deleted object within another object which uses it as a foreign key, and I use a BEFORE DELETE trigger for that. I have no idea why the code below does not work.

Even more strange is that if I perform sequentially the UPDATE query and then DELETE query, the row is deleted properly.

CREATE OR REPLACE FUNCTION cleanNoteEventConnections() RETURNS TRIGGER AS $$
DECLARE 
BEGIN
    EXECUTE 'update invoice set "noteEvent"=null where "noteEvent"=' || OLD.id;
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER cleanNoteEventConnections BEFORE DELETE ON note_event
    FOR EACH ROW EXECUTE PROCEDURE cleanNoteEventConnections();

This is what I see in the pgAdmin console after the delete query:

delete from note_event where id=34
result: Query returned successfully: 0 rows affected, 11 msec execution time.

And the note_event with id 34 still exists.

Upvotes: 0

Views: 304

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246578

This behavior is described in 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). If a nonnull value is returned then the operation proceeds with that row value.

Use RETURN OLD; instead of RETURN NULL;.

Upvotes: 3

Related Questions