Oracle 视图不可更新，关于 Replace Of 触发器的建议

Question

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

为了保持与接口系统的直接兼容性（即只需要更改数据库链接），创建了一个视图，该视图显示与旧表完全相同的列。然而，其中一些列只是模拟的，因此该视图包含如下结构：

(...)
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 插入到真实表中并忽略其他）。

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

顺便提一句。这是Oracle 11g。

Answer 1

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

请注意，某些列实际上可能在原始视图中可更新。查询 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;

视图已创建

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 触发器。

Answer 2

INSTEAD OF 触发器隐式地是“FOR EACH ROW”，因此您不必找出 WHERE 子句，只需执行以下操作：

begin
    delete base_table
    where pk = :old.pk;
end;

这也显示了 INSTEAD OF 触发器的缺点之一：它们逐行工作不是成套的。