I have a games table, that records p1_id, p1_score, p2_id, p2_score, and winner_id. I would like to have a trigger that uses a user defined function to automatically fill in the winner (as practice of using both).
I have the trigger:
CREATE TRIGGER determine_winner
BEFORE INSERT ON games
FOR EACH ROW
EXECUTE PROCEDURE calculate_winner();
and the UDF:
CREATE OR REPLACE FUNCTION calculate_winner()
RETURNS TRIGGER AS $d$
BEGIN
IF NEW.p1_score > NEW.p2_score THEN
NEW.winner_id = NEW.p1_id;
END IF;
IF NEW.p1_score < NEW.p2_score THEN
NEW.winner_id = NEW.p2_id;
END IF;
IF NEW.p1_score = NEW.p2_score THEN
NEW.winner_id = 0;
END IF;
RETURN NEW;
END;
$d$ LANGUAGE plpgsql;
This works perfectly, but I don't like at all using three IF statements, but cannot get a IF/ELSE IF or a CASE statement to work. Many examples seem to be in different versions of SQL that do not work. Can anyone help with this?
Thanks!
Aucun commentaire:
Enregistrer un commentaire