如果更新另一个表，则 ORACLE TRIGGER 更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16348182/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE TRIGGER update table if another one is updated
提问by user1069968
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:
我想在更新另一个表的所有行中更改一个字段。
我有一张比赛桌、一张裁判桌（带有nation_id）和国家桌。
现在，如果有人更新了裁判表中的国家 ID，我想从游戏表中自动更新裁判_国家名称。
我的问题是当有人更新该表中的 national_id 时，我不知道如何获取 referee_id（这是唯一的）。如果我选择所有类似 :new.nation_id 的裁判 ID，我将从生活在这个国家的人们那里获得其他裁判 ID……这是我的非工作触发器：
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;
回答by Justin Cave
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 nationtable to change the nameamong other possible holes.
当然，这并没有解决当有人更新nation表以更改name其他可能的漏洞时会发生什么。
