CODEWITHSUNDEEP
sqlfunctionpostgresqltriggers
ZAX
ZAX

Reputation: 1006

Trigger After Update In SQL Compiles But Does Not Seem To Fire

I have a trigger that should update a certain column, Points, after an update is made to a record in that same row. To do this, I have created a series of functions calc_extrapoint and calc_fieldgoalpoint to facilitate the process. Both of these functions work and return the correct values when run manually.

However, when I try to have the trigger fire after an update to the table, which most likely will happen with the columns ExtraPoint or FieldGoal the Points column is not updated as I'd expect.

For example iniitally in my table all values of each record starts as 0, 0, 0 for points, extra point, and field goal columns respectively. When I update the extra point column though to say 2, I expect the Points column to be 2. If I update again this time with field goal points to 2, I expect my Points column to be set to 8.

create or replace function calc_points() returns trigger as $$
    begin
        NEW."Points" :=  calc_extrapoint(NEW."ExtraPoint") + calc_fieldgoalpoint(NEW."FieldGoal");
    return NEW;
    end
$$ LANGUAGE plpgsql;

DROP TRIGGER calc_points on playerdata;
CREATE TRIGGER calc_points AFTER UPDATE ON playerdata
FOR EACH ROW EXECUTE PROCEDURE calc_points();

The trigger and functions all compile appropriately, but are not firing as expected. Is there something I'm missing to complete the action?

Upvotes: 0

Views: 84

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 325131

Modifying NEW in an AFTER trigger won't do anything. The row has already been written. Put a RAISE NOTICE 'Trigger fired'; in there; you'll get the notice. It's just that the trigger fires, but does nothing.

That's why we have BEFORE triggers. If you want to modify the row to be written to the table, you must use a BEFORE trigger.

Upvotes: 2

Related Questions