PostgreSQL trigger updating all table instead of row

Question

I'm trying to create a PostgreSQL trigger that updates a row whenever there's a change in some specific values.

I have a table which is has info about users, (user_code, user_email, user_name, user_password, and a field called password_reset).

My goal would be updating the password_reset field to the value "Y" and the user_password field to the value "123456", but just when there's any change in the user_name or user_email fields. I would like this to happen in the specific row where the change has been made. I've used an AFTER UPDATE trigger. However, the whole table is updated instead of the row I want.

Side note: Resetting the password to a fixed value might be a security issue. Rather create a random one you tell the user via a secure channel. And even worse, your passwords seem to be stored in clear text. Do not do that. Salt and hash them and only store the salt and hash value. — sticky bit
– sticky bit, Commented Nov 27, 2020 at 3:23
The issue is here: WHERE NEW.user_name IS DISTINCT FROM OLD.user_name . The IF test already established they are so the WHERE will always evaluate true and since you have no other conditions on the WHERE all the rows will UPDATE. — Adrian Klaver
– Adrian Klaver, Commented Nov 27, 2020 at 3:31
Answering to the side note: Thanks so much for the advice, but it's just a theoretical exercise, none of the data will be used for any real purpose... just learning to use triggers. But will keep that in mind for the future! :) — robert_gonzalez
– robert_gonzalez, Commented Nov 27, 2020 at 13:14

Laurenz Albe · Accepted Answer · 2020-11-27 03:02:31Z

1

If you want to modify the new row, you should use a BEFORE trigger rather than trying to UPDATE the table:

CREATE FUNCTION trial() RETURNS trigger AS
$$BEGIN
    IF NEW.user_name IS DISTINCT FROM OLD.user_name OR
       NEW.user_email IS DISTINCT FROM OLD.user_email
    THEN
        NEW.password_reset := 'Y';
        NEW.user_password := '123456';
    END IF;

    RETURN NEW; 
END;$$ LANGUAGE plpgsql;

CREATE TRIGGER trial1 BEFORE UPDATE OF user_name, user_email
    ON tb_user FOR EACH ROW EXECUTE PROCEDURE trial();

answered Nov 27, 2020 at 3:02

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL trigger updating all table instead of row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related