You create instead of triggers on views. This enables you to insert/update rows in the underlying tables where this normally would raise an exception.
For example, this creates a view joining departments to employees. By default, you can't rows into the view.
Adding an instead of insert trigger intercepts DML on the view and redirects it to the underlying tables.
There should only be one row/department, so I've made it MERGE instead of inserting into this table. This then updates the department name with the new values.
If you only want to insert new departments and leave existing ones as-is, change this to an INSERT. And ensure DEPARTMENTS has appropriate primary/unique constraints so you can catch the ORA-00001 error:
create table departments (
department_id int, department_name varchar2(30)
);
create table employees (
employee_id int, department_id int
);
insert into departments values ( 1, 'New dept' );
insert into employees values ( 1, 1 );
commit;
create or replace view department_employees as
select * from departments d
join employees e
using ( department_id );
select * from department_employees;
/*
DEPARTMENT_ID DEPARTMENT_NAME EMPLOYEE_ID
------------- ------------------------------ -----------
1 New dept 1
*/
insert into department_employees ( department_id, department_name, employee_id )
values ( 2, 'Second dept', 2 );
--ORA-01779: cannot modify a column which maps to a non key-preserved table
create or replace trigger deem_instead_of_ins_t
instead of insert on department_employees
for each row
begin
merge into departments
using dual
on ( department_id = :new.department_id )
when not matched then
insert values ( :new.department_id, :new.department_name )
when matched then
update set department_name = :new.department_name;
insert into employees values ( :new.employee_id, :new.department_id );
end;
/
insert into department_employees ( department_id, department_name, employee_id )
values ( 2, 'Second dept', 2 );
insert into department_employees ( department_id, department_name, employee_id )
values ( 1, 'Changed name', 3 );
select * from department_employees
order by department_id;
/*
DEPARTMENT_ID DEPARTMENT_NAME EMPLOYEE_ID
------------- ------------------------------ -----------
1 Changed name 1
1 Changed name 3
2 Second dept 2
*/This method can be used when refactoring database schemas. For example, when splitting the columns from one table into two tables.
I've used this in the past to create a view which joins the tables to give the original view of the data. Then added an instead of insert trigger to redirect DML as needed. e.g.
create table new_table as
select columns_to_remove from original_table;
rename original_table to original_table_t;
create view original_table as
select ot.id, nt.columns_to_remove
from original_table_t ot
join new_table nt
where ...;
create or replace trigger original_table_ins_t
instead of insert on original_table
for each row
begin
...
end;
This allows you to change the schema with
zero code changes! From the application's perspective, it's still accessing ORIGINAL_TABLE as it did when the view was a table.
I'd view this more as a stop-gap solution; ultimately the plan was to change the code to use the underlying tables, not the view. Switching to a view with instead of triggers was much faster than making the associated code changes.