Writing an SQL trigger to compare old and new values

Question

I am trying to write a SQL trigger that compares the old and new values. If the two values are different then I need to display an error saying that you can't update the names. The exact definition of my trigger is

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.

The table that I am using for this problem is:

  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)

Here is my code:

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 can use raise exception to, um, raise exceptions.

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;

You surely don't want to disallow changes to the team name on insert. (PostgreSQL won't let you do it anyway.)

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