CODEWITHSUNDEEP
sqlpostgresql
Jayaram
Jayaram

Reputation: 6606

Postgres trigger not updating specified column

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.

Upvotes: 5

Views: 2574

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

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.

Upvotes: 9

Related Questions