CODEWITHSUNDEEP
sqlpostgresqlfunctiontriggers
robert_gonzalez
robert_gonzalez

Reputation: 27

PostgreSQL trigger updating all table instead of row

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.

Upvotes: 0

Views: 77

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246083

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();

Upvotes: 1

Related Questions