Oracle 视图不可更新，关于替代触发器的建议
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5260148/
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 view not updatable, advice on Instead Of triggers
提问by Jules
after migrating a system/database we modified a central table which has been used for interfacing with 15 different systems. We used this migration to add and delete a few fields in this table.
迁移系统/数据库后，我们修改了一个中央表，该表已用于与 15 个不同的系统进行交互。我们使用此迁移来添加和删除此表中的一些字段。
To maintain direct compatibility with the interfacing systems (i.e. only need to change the database-link), a view has been created which shows the exact same columns as the old table had. However, some of these columns are only emulated, so the view contains constructs like these:
为了保持与接口系统的直接兼容性（即只需要更改数据库链接），已创建一个视图，该视图显示与旧表完全相同的列。但是，其中一些列仅被模拟，因此视图包含如下结构：
(...)
CREATE OR REPLACE VIEW STAFF_DATA_COMPAT AS
SELECT
NVL(knownas_surname,surname) as surname,
first_name
middle_name as mid-name
NULL as ni,
NULL as home_tel_no,
(...)
Obviously, this view is not inherently updatable.
显然，这个视图本质上是不可更新的。
I do understand, that you need INSTEAD OF triggers for all DML (insert, update, delete) statements. I can see, that a INSTEAD OF INSERT trigger should be quite straightforward (just inserting :NEW.field to the real table, where appropriate and ignoring the others).
我明白，您需要为所有 DML（插入、更新、删除）语句使用 INSTEAD OF 触发器。我可以看到， INSTEAD OF INSERT 触发器应该非常简单（只需在适当的情况下将 :NEW.field 插入到真实表中并忽略其他触发器）。
But the actual question: How to write the according INSTEAD OF UPDATE/DELETE triggers?For instance, how do I take over the "WHERE" clause of an original DELETE statement? Is there anything else I should worry about, any side-effects when using these triggers?
但实际问题是：如何编写相应的 INSTEAD OF UPDATE/DELETE 触发器？例如，我如何接管原始 DELETE 语句的“WHERE”子句？使用这些触发器时还有什么我应该担心的吗？
Btw. It's Oracle 11g.
顺便提一句。这是 Oracle 11g。
回答by Vincent Malgrat
The INSTEAD OF trigger would look like this (I've assumed you have a primary key column id):
INSTEAD OF 触发器看起来像这样（我假设您有一个主键列id）：
SQL> CREATE OR REPLACE TRIGGER trg_staff_data_cpt_instead_upd
2 INSTEAD OF UPDATE ON staff_data_compat
3 FOR EACH ROW
4 BEGIN
5 UPDATE staff_data_compat_t
6 SET knownas_surname = :new.surname,
7 first_name = :new.first_name,
8 middle_name = :new.mid_name
9 WHERE id = :new.id
10 END;
11 /
Trigger created
Note that some columns may in fact be updatable in the original view. Query the all_updatable_columnsview (before creating the trigger) to find out:
请注意，某些列实际上可能在原始视图中是可更新的。查询all_updatable_columns视图（在创建触发器之前）以找出：
SQL> CREATE TABLE staff_data_compat_t AS
2 SELECT object_name knownas_surname,
3 owner surname,
4 object_type first_name,
5 subobject_name middle_name
6 FROM all_objects;
Table created
SQL> CREATE OR REPLACE VIEW staff_data_compat AS
2 SELECT
3 NVL(knownas_surname,surname) as surname,
4 first_name,
5 middle_name mid_name,
6 NULL as ni,
7 NULL as home_tel_no
8 FROM staff_data_compat_t;
View created
视图已创建
SQL> SELECT * FROM all_updatable_columns WHERE table_name = 'STAFF_DATA_COMPAT';
OWNER TABLE_NAME COLUMN_NAME UPDATABLE INSERTABLE DELETABLE
------ ------------------ ------------ --------- ---------- ---------
VNZ STAFF_DATA_COMPAT SURNAME NO NO NO
VNZ STAFF_DATA_COMPAT FIRST_NAME YES YES YES
VNZ STAFF_DATA_COMPAT MID_NAME YES YES YES
VNZ STAFF_DATA_COMPAT NI NO NO NO
VNZ STAFF_DATA_COMPAT HOME_TEL_NO NO NO NO
If you only need to insert/update these columns, you don't need an INSTEAD OF trigger.
如果您只需要插入/更新这些列，则不需要 INSTEAD OF 触发器。
回答by Tony Andrews
INSTEAD OF triggers are implicitly "FOR EACH ROW", so you don't have to find out the WHERE clause, you just do something like this:
INSTEAD OF 触发器隐式为“FOR EACH ROW”，因此您不必找出 WHERE 子句，您只需执行以下操作：
begin
delete base_table
where pk = :old.pk;
end;
This also shows one of the drawbacks of INSTEAD OF triggers: they work row-by-row not in sets.
这也显示了 INSTEAD OF 触发器的缺点之一：它们逐行工作，而不是成组工作。
