Trigger for Boolean Update (Postgresql)-CodePudding

I am creating a sports database and am trying to add a trigger to my DDL. The aim of the trigger is to insert values into a table (commitment) whenever another table (player) has a college commitment (col_commit) change from FALSE to TRUE. Whenever this happens I want to insert the player's id and the date of the update. This is what I have so far:

CREATE OR REPLACE FUNCTION commitment_log()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
    BEGIN
        IF OLD.col_commit = TRUE THEN
            INSERT INTO commitment(player_id, committed_date)
            VALUES (OLD.player_id, now());
        END IF;
        
        RETURN NEW;
    END;
$$;

CREATE TRIGGER commitment_trigger
BEFORE UPDATE
ON player
FOR EACH ROW
EXECUTE PROCEDURE commitment_log();

As of now, when I update a player's commitment in the player table to TRUE, nothing is updated in the commitment table.

CodePudding user response：

You're checking old.col_commit=TRUE instead of new.col_commit=TRUE and old.col_commit=FALSE that you described. You can also move this to the trigger WHEN condition:

CREATE OR REPLACE FUNCTION commitment_log()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
    BEGIN
        INSERT INTO commitment(player_id, committed_date)
        VALUES (OLD.player_id, now());        
        RETURN NEW;
    END;
$$;

CREATE TRIGGER commitment_trigger
BEFORE UPDATE
ON player
FOR EACH ROW WHEN (new.col_commit IS True AND old.col_commit IS False)
EXECUTE PROCEDURE commitment_log();

Demo

CodePudding user response：

OLD refers to the data that where before the update and NEW to the updated values. As you only check the OLD.col_commit data, that is FALSe, you never will get a log entry.

so simply change the torgger to chekc for NEW.col_commit

CREATE OR REPLACE FUNCTION commitment_log()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
    BEGIN
        IF NEW.col_commit = TRUE THEN
            INSERT INTO commitment(player_id, committed_date)
            VALUES (OLD.player_id, now());
        END IF;
        
        RETURN NEW;
    END;
$$;

CREATE TRIGGER commitment_trigger
BEFORE UPDATE
ON player
FOR EACH ROW
EXECUTE PROCEDURE commitment_log();

see sample fiddle