CODEWITHSUNDEEP
oracle-databaseplsql
Jules
Jules

Reputation: 1371

Oracle view not updatable, advice on Instead Of triggers

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

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

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?

Btw. It's Oracle 11g.

Upvotes: 3

Views: 9155

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132670

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:

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.

Upvotes: 6

Vincent Malgrat
Vincent Malgrat

Reputation: 67762

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

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;

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.

Upvotes: 8

Related Questions