I want to change a field in all rows of a table when updating another one.
I have a game table, a referee table (with an nation_id) and the nation table.
Now I want to auto update the referee_nation_Name from the game table if someone update the nation_id in the referee table.
My problem is i have no idea how to get the referee_id (which is unique) when someone update the nation_id in this table. If i select all referee_id which are like :new.nation_id i will get the other referee ids from peoples who life in this country... here is my non working trigger:
CREATE OR REPLACE TRIGGER name
AFTER UPDATE OF nation_id ON referee
FOR EACH ROW
SELECT n.name INTO nationname
FROM nation n, referee r, game g
WHERE n.nation_id = r.nation_id AND g.referee_id = :old.referee_id);
UPDATE game SET referee_nation_Name = nationname WHERE referee_id = :old.referee_id;
Knowing that you would never design a data model that stored denormalized data like this precisely because it is incredibly hard to keep in sync, your instructor is probably looking for something like
CREATE OR REPLACE TRIGGER name BEFORE UPDATE OF nation_id ON referee FOR EACH ROW DECLARE l_nationname VARCHAR2(150); BEGIN SELECT name INTO l_nationname FROM nation WHERE nation_id = :new.nation_id; UPDATE game SET referee_nation_name = l_nationname WHERE referee_id = :new.referee_id; END;
Of course, this doesn't address what happens when someone updates the
nation table to change the
name among other possible holes.