After update trigger to replace an empty string with null updates all the data in that column to null

Question

I want to update the record of a particular user only. But the update trigger fires for all the users.

CREATE OR REPLACE FUNCTION replace_empty_username_with_null() RETURNS TRIGGER
    LANGUAGE plpgsql AS
BEGIN
    UPDATE user_temp
    SET username=null
    WHERE NEW.id = OLD.id and NEW.username = '';

    RETURN NEW;
END
CREATE TRIGGER replace_empty_username
    AFTER UPDATE OF username ON user_temp
    FOR EACH ROW
    WHEN (NEW.username = '' AND OLD.id = NEW.id)
EXECUTE PROCEDURE replace_empty_username_with_null();

score 0 · Accepted Answer · answered Feb 21 '20 at 03:39

The expression NEW.id = OLD.id makes sense in the WHEN clause of the trigger ("ID did not change and is not null"). But it makes no sense in the body of the trigger function, where it burns down to TRUE. Also, to only affect the row which is being updated, just use a BEFORE trigger instead:

CREATE OR REPLACE FUNCTION replace_empty_username_with_null()
  RETURNS TRIGGER LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.username = null;  -- !
   RETURN NEW;
END
$func$

CREATE TRIGGER replace_empty_username
BEFORE UPDATE OF username ON user_temp -- !
FOR EACH ROW
WHEN (NEW.username = '' AND OLD.id = NEW.id)
EXECUTE PROCEDURE replace_empty_username_with_null();

PostgreSQL Update trigger

After update trigger to replace an empty string with null updates all the data in that column to null

1 Answers1