How do i properly set up a trigger?

Question

I am pretty new to this topic. So i have this table:

                             Table "public.client"
          Column          |       Type        | Collation | Nullable | Default 
--------------------------+-------------------+-----------+----------+---------
 contact_phone            | character(11)     |           | not null | 
 status                   | character(11)     |           | not null | 
 money_spent              | real              |           | not null | 
 bonus                    | real              |           | not null | 
 client_name              | character varying |           | not null | 
 assigned_employee_number | character(11)     |           |          | 

I want to update column "bonus" each time column "money_spent" gets updated. These are my function and trigger:

CREATE OR REPLACE FUNCTION client_bonus_update() RETURNS trigger AS $client_bonus_update$
    BEGIN
        NEW.bonus := NEW.money_spent * 0.1;
        RETURN NEW;
    END;
$client_bonus_update$ LANGUAGE plpgsql;


CREATE TRIGGER client_bonus_update
AFTER UPDATE OF money_spent
ON client
FOR ROW EXECUTE FUNCTION client_bonus_update();

It does not work. Default values of columns "money_spent" and "bonus" are 0, and on update bonus does not change. How should i write the code for my function and trigger?

Answer 1

You're trying to update the NEW.bonus value after the update using AFTER UPDATE, though, this will not affect the updated row in the table because the update has already occurred.

What you really need to be looking into for your usecase would be the BEFORE UPDATE trigger, which lets you modify the values before they are written to the table

CREATE OR REPLACE FUNCTION client_bonus_update()
RETURNS trigger AS $client_bonus_update$
BEGIN
    NEW.bonus := NEW.money_spent * 0.1;
    RETURN NEW;
END;
$client_bonus_update$ LANGUAGE plpgsql;

CREATE TRIGGER client_before_update_trigger
BEFORE UPDATE OF money_spent ON client
FOR EACH ROW
EXECUTE FUNCTION client_bonus_update();

This makes the trigger will calculate the new bonus value before the update is applied