CODEWITHSUNDEEP
postgresqltriggersplpgsql
ryan
ryan

Reputation: 99

Creating a specific exception for a function in PL/PgSQL

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. That is the problem that I seem to be having, I don't understand how to display an error for an exception in PSQL 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.

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)

The code I have right now, I need help for telling the user that they aren't allowed to change team names, but I am having issues doing so.

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

Upvotes: 0

Views: 54

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324881

You need the RAISE statement to raise an exception. There are examples in the manual.

RAISE EXCEPTION ....

(This looks like homework and you've asked about homework before, so I'm intentionally not giving a complete answer).

Upvotes: 0

Related Questions