ERROR: tuple to be deleted was already modified by an operation triggered by the current command

Question

I have table user that has deleted_at field. To implement the soft deletion I decided to create trigger function that will modify deleted_at field instead of physical deletion. But after that I get such error:

ERROR:  tuple to be deleted was already modified by an operation triggered by the current command
HINT:  Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows.

In hint it is said that I can use AFTER trigger but it's not for me as I need to update field before deletion.

So here's my code:

CREATE OR REPLACE FUNCTION userDeleted() RETURNS trigger AS $$
    BEGIN
        UPDATE user_schema.user_account
        SET deleted_at = NOW()
        WHERE user_id = OLD.user_id;

        RETURN OLD;
    END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER user_soft_delete_trigger
    BEFORE DELETE ON user_schema.user_account
    FOR EACH ROW
    EXECUTE PROCEDURE userDeleted();

I also tried to create trigger like in this question

tuple to be updated was already modified by an operation triggered by the current command

But it still not working.

So what can be the problem? If you know, please, tell me. I'd really appreciate it!

Answer 1

The problem is that you

RETURN OLD;

so that the DELETE is actually executed. Instead, use

RETURN NULL;

to abandon the DELETE.