多个表上的 Oracle 触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19154855/
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 on multiple tables
提问by user2698645
i have 3 tables A,B,C. I have a stored procedure of these 3 tables with join condition inserted in 4th table D. now what i want is "ANY INSERT OR UPDATE"made to these 3 tables(A,B,C) should update 4th table D. Can any one please tel how to write trigger in pl/sql?
我有 3 张桌子 A、B、C。我有这 3 个表的存储过程，并在第 4 个表 D 中插入连接条件。现在我想要的是对这 3 个表（A、B、C）进行的“任何插入或更新”应该更新第 4 个表 D。任何人都可以请告诉如何在 pl/sql 中编写触发器？
回答by Trinimon
You need three triggers, that do the required updates on D. E.g. for table Athis might be:
您需要三个触发器，在D. 例如对于表，A这可能是：
CREATE OR REPLACE TRIGGER trg_ins_upd_A
AFTER INSERT OR UPDATE ON A
FOR EACH ROW
DECLARE
-- required declarations
BEGIN
IF INSERTING THEN
UPDATE D
SET column1 = :new.column
WHERE column1 = :old.column ...
END IF;
IF UPDATING THEN
-- do required updates on D here
END IF;
END;
If you have a stored procedure that performs this update you might implement something similar like this:
如果您有一个执行此更新的存储过程，您可能会实现类似如下的内容：
CREATE OR REPLACE TRIGGER trg_ins_upd_A
AFTER INSERT OR UPDATE ON A
FOR EACH ROW
DECLARE
-- required declarations
BEGIN
IF INSERTING THEN
my_procedure_one (...);
END IF;
IF UPDATING THEN
my_procedure_two (...);
END IF;
END;
回答by David Aldridge
Depending on the query complexity for creating the fourth table's data, this may be better implemented (more robustly, with greater flexibility and less code) with an on-commit fast refresh materialised view.
根据创建第四个表数据的查询复杂性，这可能会通过提交时快速刷新物化视图更好地实现（更健壮，具有更大的灵活性和更少的代码）。
http://docs.oracle.com/cd/B28359_01/server.111/b28326/repmview.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28326/repmview.htm
