Postgres trigger not updating specified column

Question

I have the below user table

  Column   |           Type           |                       Modifiers                       
-----------+--------------------------+-------------------------------------------------------
 id        | integer                  | not null default nextval('accounts_id_seq'::regclass)
 username  | character varying(40)    | 
 email     | character varying(40)    | 
 password  | character varying        | 
 join_date | timestamp with time zone | default now()
 xp        | integer                  | default 0
 level     | integer                  | default 1
 provider  | character varying        | 
 is_admin  | boolean                  | default false

I'm trying to create a trigger which looks at the user xp INTEGER and updates his level based on the given xp

this is my below function and necessary trigger

CREATE OR REPLACE FUNCTION set_user_level() RETURNS TRIGGER AS $level$

  BEGIN

    CASE 
      WHEN NEW.xp BETWEEN 100 AND 200 THEN
        NEW.level = 2;
      WHEN NEW.xp BETWEEN 200 AND 400 THEN
        NEW.level = 3;
      ELSE
        NEW.level = 1;
    END CASE;

    RETURN NEW;
  END;
$level$ LANGUAGE plpgsql;

CREATE TRIGGER set_user_level AFTER UPDATE ON account FOR EACH ROW EXECUTE PROCEDURE set_user_level();

However , whenever i update the user xp . The user level still stays at 1. Is there something wrong with my syntax? I pretty much used the same syntax as specified in the docs.

Raphaël Althaus · Accepted Answer · 2016-07-22 15:14:09Z

9

You should use a BEFORE instead of an AFTER update in your case, and your code will work perfectly.

See http://sqlfiddle.com/#!15/ef408/1

You may read a bit about triggers here.

Interesting sample :

Typically, row-level BEFORE triggers are used for checking or modifying the data that will be inserted or updated. For example, a BEFORE trigger might be used to insert the current time into a timestamp column, or to check that two elements of the row are consistent. Row-level AFTER triggers are most sensibly used to propagate the updates to other tables, or make consistency checks against other tables. The reason for this division of labor is that an AFTER trigger can be certain it is seeing the final value of the row, while a BEFORE trigger cannot; there might be other BEFORE triggers firing after it. If you have no specific reason to make a trigger BEFORE or AFTER, the BEFORE case is more efficient, since the information about the operation doesn't have to be saved until end of statement.

answered Jul 22, 2016 at 15:14

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jayaram Over a year ago

Thank you for your answer .. i was thinking of a whole new meaning of BEFORE and AFTER. ill try this right away .. also , would you by any chance be able to clarify what exactly the $level$ syntax means when creating the function? that is something which i'm unable to figure out but is specified in the docs

Raphaël Althaus Over a year ago

See postgresql.org/docs/current/static/… .

Josh Pachner Over a year ago

I was stumped for a bit, and then i realized i had my trigger firing off on STATEMENT and not for each ROW. And so modifying the new in the postgres function wasnt doing anything

Collectives™ on Stack Overflow

Postgres trigger not updating specified column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related