CODEWITHSUNDEEP
postgresqltriggers
rudwna
rudwna

Reputation: 117

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

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

Upvotes: 1

Views: 3309

Answers (1)

Simo Kivist&#246;
Simo Kivist&#246;

Reputation: 4453

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;

Upvotes: 1

Related Questions