Oracle视图不可更新，有关“代替触发器"的建议 [英] Oracle view not updatable, advice on Instead Of triggers

问题描述

在迁移系统/数据库后，我们修改了一个中央表，该表已用于与15个不同的系统进行接口连接.我们使用此迁移来添加和删除此表中的一些字段.

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.

为了保持与接口系统的直接兼容性(即仅需要更改数据库链接)，已创建一个视图，该视图显示与旧表完全相同的列.但是，其中某些列仅是模拟的，因此视图包含如下构造:

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,
(...)

很明显，此视图不是本质上不可更新的.

我确实知道，您需要对所有DML(插入，更新，删除)语句使用INSTEAD OF触发器. 我可以看到，INSTEAD OF INSERT触发器应该非常简单(只要在适当的地方将:NEW.field插入到实际表中，而忽略其他).

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).

但是实际问题是:如何编写相应的INSTEAD OF UPDATE/DELETE触发器?例如，如何接管原始DELETE语句的"WHERE"子句?使用这些触发器时，我还有什么需要担心的副作用吗?

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?

顺便说一句.是Oracle 11g.

Btw. It's Oracle 11g.

推荐答案

INSTEAD OF触发器如下所示(我假设您有主键列id):

The INSTEAD OF trigger would look like this (I've assumed you have a primary key column 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

请注意，实际上某些列在原始视图中可能是可更新的.在创建触发器之前，查询all_updatable_columns视图以找出:

Note that some columns may in fact be updatable in the original view. Query the all_updatable_columns view (before creating the trigger) to find out:

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;

创建视图

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

如果只需要插入/更新这些列，则不需要INSTEAD OF触发器.

If you only need to insert/update these columns, you don't need an INSTEAD OF trigger.

这篇关于Oracle视图不可更新，有关“代替触发器"的建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！