Question 251944 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How do i properly set up a trigger?

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    The accepted answer is completely valid.

    But you can take this a step further and eliminate additional code (trigger) altogether. You can redefine bonus as a generated column. (Providing you Postgres is v11 or greater). So

    alter table clients 
      drop column bonus;

alter table clients 
      add column bonus real
        generated always as  (money_spent*0.1) stored;

    Now Insert or Update will automatically calculate bonus. Further it cannot by itself be updated, that is be a target of set. Thus it cannot cannot be out-of-step with money_spent. Demo here.
    The potential downside being that if you inappropriately use select * the order of the columns has changed. The solution to that is appropriately name your columns on select.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search