updatable views and instead of triggers

I am data-modeling those days.

Each table has it own description table (yes, it is Java). To keep it simple, FRUIT[id,description] and MEAT[id,description] (35 of them right now). There must be one table which contains all descriptions in it GLOBAL[type,id,description]. I wish to preserve the data quality. Both the little (FRUIT,MEAT) and the big (GLOBAL) must be selectable and editable. Someone, the little ones are views of the big one, or the big one is a view of the little ones.

Plan 1: there is one big table and many small views

create table global(
type varchar2(15),
id number,
description varchar2(10),
primary key(type,id));
create or replace view fruit as
select id,description
from global
where type='fruit';
create or replace view meat as
select id,description
from global
where type='meat';


plan 2: one view which union all all little tables

create table fruit(
id number,
description varchar2(10),
primary key(id));
create table meat(
id number,
description varchar2(10),
primary key(id));
create or replace view global as
select 'fruit' type,id,description
from fruit
union all
select 'meat',id,description
from meat;


Is this fine? As long as I only select rows, both are possible. However, a select * from fruit will take much longer if fruit is a view and the rows are retrieved from a huge table, probably per Index scan.

In the solution 2, I keep small lookup tables, and it should be faster to retrieve data from those. However, the UNION ALL view is not updatable.

In solution 1, the small views seem updatable, but it will not work.

Let’s see

SQL> create table global(
2 type varchar2(15),
3 id number,
4 description varchar2(10),
5 primary key(type,id));

SQL> insert into fruit values(1,'Mango');
insert into fruit values(1,'Mango')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("AUDBA"."GLOBAL"."TYPE")

I cannot tell the view to default the type to fruit when inserting in fruit

In plan 2, the view is not updatable at all

SQL> create table fruit(
2 id number,
3 description varchar2(10),
4 primary key(id));

SQL> insert into global values(
2 'meat',1,'beef');
insert into global values(
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


So the solution is called INSTEAD OF trigger. An instead of trigger is a very special trigger specific to views. It will make all views updatable, how magic!

As a DBA, I am not very confident with triggers, because the developer is doing the consistency check, no more the Oracle Engine. It will therefore be much less performant and it may contain errors. Here I write a trigger for each case. They certainly have bugs. I am myself not convinced by using triggers at all. Those kind of DML home-made rewrites are just too dangerous, but what else can I do…

OK, plan 1: one instead of trigger for every single view

create or replace trigger fruit_insteadof
instead of insert or update or delete on fruit
begin
if inserting then
insert into global
values('fruit',:new.id,:new.description);
elsif deleting then
delete from global
where type='fruit'
and id=:old.id
and description=:old.description;
elsif updating then
update global
set type='fruit',
id=:new.id,
description=:new.description
where type='fruit'
and id=:old.id
and description=:old.description;
end if;
end;
/


Plan 2: One instead of trigger for the global view
create or replace trigger global_insteadof
instead of insert or update or delete on global
begin
if inserting then
if (:new.type='fruit') then
insert into fruit values(
:new.id,:new.description);
elsif (:new.type='meat') then
insert into meat values(
:new.id,:new.description);
else
raise_application_error(-20001,
'Trigger cannot insert into '||:new.type);
end if;
elsif deleting then
if (:old.type='fruit') then
delete fruit where
id=:old.id
and description=:old.description;
elsif (:old.type='meat') then
delete meat where
id=:old.id
and description=:old.description;
else
raise_application_error(-20001,
'Trigger cannot delete from '
|| :old.type);
end if;
elsif updating then
if (:new.type!=:old.type) then
delete from global
where type=:old.type
and id=:old.id
and description=:old.description;
insert into global values(
:new.type,:new.id,:new.description);
elsif (:new.type='fruit') then
update fruit set
id=:new.id,
description=:new.description
where id=:old.id
and description=:old.description;
elsif (:new.type='meat') then
update meat set
id=:new.id,
description=:new.description
where id=:old.id
and description=:old.description;
else
raise_application_error(-20001,
'Trigger cannot update '||:old.type);
end if;
end if;
end;


I am not at all convinced about the data consistency in such an approach.

Well, I will try to push a solution with no GLOBAL table. We probably do not need it.