sql - Postgres 触发器不更新指定的列

Question

我有下面的用户表

  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

我正在尝试创建一个触发器，它查看用户 xp INTEGER 并根据给定的 xp 更新他的级别

这是我的以下功能和必要的触发器

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();

但是，每当我更新用户 xp 时。用户级别仍然停留在1。我的语法有问题吗？我几乎使用了文档中指定的相同语法。

Answer 1

在您的案例中，您应该使用 BEFORE 而不是 AFTER 更新，您的代码将完美运行。

参见 http://sqlfiddle.com/#!15/ef408/1

您可能会阅读一些有关触发器的内容 here .

有趣的例子:

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.