sql - 编写一个 SQL 触发器来比较旧值和新值

Question

我正在尝试编写一个比较旧值和新值的 SQL 触发器。如果这两个值不同，那么我需要显示一条错误消息，提示您无法更新名称。我的触发器的确切定义是

write a trigger function named disallow_team_name_update that compares the OLD and NEW records team fields. If they are different raise an exception that states that changing the team name is not allowed. Then, attach this trigger to the table with the name tr disallow team name update and specify that it fires before any potential update of the team field in the table.

我为这个问题使用的表是:

  Table "table.group_standings"
    Column |         Type          | Modifiers
   --------+-----------------------+-----------
    team   | character varying(25) | not null
    wins   | smallint | not null   
    losses | smallint | not null
    draws  | smallint | not null
    points | smallint| not null
   Indexes:
     "group_standings_pkey" PRIMARY KEY, btree (team)
  Check constraints:
   "group_standings_draws_check" CHECK (draws >= 0)
   "group_standings_losses_check" CHECK (losses >= 0)
   "group_standings_points_check" CHECK (points >= 0)
   "group_standings_wins_check" CHECK (wins >= 0)

这是我的代码:

CREATE OR REPLACE FUNCTION disallow_team_name_update() RETURNS trigger AS $$
    BEGIN
            if(NEW.team <> OLD.team)
            /*tell the user to not change team names*/

    END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER tr_disallow_team_name_update BEFORE INSERT OR UPDATE OF team ON group_standings 
FOR EACH ROW EXECUTE PROCEDURE disallow_team_name_update();

Answer 1

PostgreSQL 可以使用 raise exception 来，嗯，引发异常。

CREATE OR REPLACE FUNCTION disallow_team_name_update() 
RETURNS trigger AS 
$$
    BEGIN
        if(NEW.team <> OLD.team) then
            raise exception 'Invalid update.'
            using hint = 'Changes to team name are not allowed.';
        end if;

    END
$$ 
LANGUAGE plpgsql;

您肯定不想在 insert 上禁止更改团队名称。 (无论如何，PostgreSQL 不会让你这样做。)

CREATE TRIGGER tr_disallow_team_name_update 
BEFORE UPDATE OF team ON group_standings 
FOR EACH ROW EXECUTE PROCEDURE disallow_team_name_update();