PostgreSQL trigger have the same OLD/NEW values when explicitly update a column

Question

I have a table with a boolean column and an UPDATE trigger. When I try to explicitly update the column value from FALSE to TRUE, the values in the column are correct, i.e. OLD being FALSE and NEW being TRUE. When I try to change from TRUE to FALSE, both OLD and NEW will be FALSE. I am using NOTIFY to check these values inside trigger.

Has anyone experienced this weird behavior?

Below are some of the trigger function, the business logic I thought irrelevant are omitted:

CREATE OR REPLACE FUNCTION locker_before_state_update()
RETURNS TRIGGER
AS $$
DECLARE 
    l_reservation_id INT;
BEGIN
    -- for debugging
    PERFORM pg_notify('locker_is_alive_update', 'N' || NEW);
    PERFORM pg_notify('locker_is_alive_update', 'O' || OLD);

    -- this check will fail when update from TRUE to FALSE
    IF (NEW.state = OLD.state) AND (OLD.is_alive <> NEW.is_alive) THEN
        PERFORM pg_notify('locker_is_alive_update', NEW.id || ',' || NEW.is_alive);
        RETURN NULL;
    END IF;

    ...

RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER locker_before_state_update_trigger BEFORE UPDATE ON locker FOR EACH ROW EXECUTE PROCEDURE locker_before_state_update();

The conditions in your if statement would fail if any of the columns is null. Could that be the case? — user330315
– user330315, Commented Mar 17, 2015 at 15:13
@a_horse_with_no_name Nope, all the column values are not null. — rudwna
– rudwna, Commented Mar 17, 2015 at 15:22

Simo Kivistö · Accepted Answer · 2015-03-21 19:58:17Z

1

You should experience weird behaviour because you are returning NULL inside your if-statement. Actually what happens is that the update doesn't get done at all. I tested it by replacing RETURN NULL with RETURN NEW and it worked as expected. The if-part:

IF (NEW.state = OLD.state) AND (OLD.is_alive <> NEW.is_alive) THEN
    PERFORM pg_notify('locker_is_alive_update', NEW.id || ',' || NEW.is_alive || ',' || OLD.is_alive);
    RETURN NEW;
END IF;

answered Mar 21, 2015 at 19:58

Simo Kivistö

4,5933 gold badges41 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL trigger have the same OLD/NEW values when explicitly update a column

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