user1069968 user1069968 - 5 months ago 13
SQL Question

ORACLE TRIGGER update table if another one is updated

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
DECLARE
nationname VARCHAR2(150);
BEGIN
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;
END;

Answer

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.