CODEWITHSUNDEEP
sqlpostgresqltriggers
Dominick Munson
Dominick Munson

Reputation: 17

Trigger for Boolean Update (Postgresql)

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.

Upvotes: 1

Views: 567

Answers (2)

nbk
nbk

Reputation: 49375

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 trigger to check for NEW.col_commit

Belayer is right, this should be an AFTER UPDATE TRIGGER vecause, it will only be run when the update was commited, a BEFORE UPDATE TRIGGER will run before the commit and could change the data

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
AFTER UPDATE
ON player
FOR EACH ROW
EXECUTE PROCEDURE commitment_log();

see sample fiddle

Of course, if you want only to capture the changes from FALSE to TRUE, you need also to check the OLD value

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

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

Upvotes: 0

Zegarek
Zegarek

Reputation: 26133

You're checking old.col_commit=TRUE instead of new.col_commit=TRUE and old.col_commit=FALSE that you described.

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

Basic demo

I didn't initially stress this, but specifying your condition in trigger WHEN section as I suggest above is just better for performance. If you do it at the beginning of trigger body, it'll always fire on update, adding overhead, even if it's not needed.

explain analyze update player set col_commit=False;--90000 rows

Update on player p  (cost=0.00..13817.54 rows=0 width=0) (actual time=1910.569..1910.570 rows=0 loops=1)
  ->  Seq Scan on player p  (cost=0.00..13817.54 rows=983554 width=7) (actual time=1.729..52.606 rows=90000 loops=1)
Planning Time: 0.086 ms
Trigger commitment_trigger: time=240.780 calls=90000
Execution Time: 1910.603 ms

If you do it in WHEN, it'll do the check and abort without firing and incurring costs related to that. Note shorter execution time and no trigger calls compared to the earlier plan:

explain analyze update player set col_commit=False;--90000 rows

Update on player p  (cost=0.00..6908.77 rows=0 width=0) (actual time=1130.119..1130.120 rows=0 loops=1)
  ->  Seq Scan on player p  (cost=0.00..6908.77 rows=491777 width=7) (actual time=14.578..43.190 rows=90000 loops=1)
Planning Time: 0.057 ms
Execution Time: 1130.148 ms--(no trigger calls, shorter execution time)

See this demo.

Upvotes: 1

Related Questions