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

Table created.

SQL> create or replace view fruit as
2 select id,
3 description
4 from global
5 where type='fruit';

View created.

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

Table created.

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

Table created.

SQL> create or replace view global as
2 select 'fruit' type,id,description
3 from fruit
4 union all
5 select 'meat',id,description
6 from meat;

View created.

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.

By Laurent Schneider

Oracle Certified Master

5 comments

  1. I have previously used a similar approach on the content model for a news website publshing system where the shared attributes of the different content types were held in a single table and the extra individual attributes were held in tables for each specific content type (where content type were things like articles, adverts, flash thing, and loads more).

    One of the additional reasons for this was that the 3rd party content management technology imposed certain restrictions on the data model which I though were best abstracted away into the views rather than the base tables (difficult to explain now and hard to remember).

    One of the pains that I had was the the INSTEAD OF triggers called stored procedures to do the inserts and I never got around to writing a management api so that you could call a proc to add a column to the base shared attribute table which would then automatically add the new attribute to the various triggers and procs.

  2. Laurent,

    The Global table approach is what I have always gone with – pretty scared about triggers in Production. However, it will be good to knowif anyone has had a successful trigger implementation.

  3. I think , the global table is more accetable. If you have the application running on oracle. just make the table partition by list . and you won’t have to worry on performance.

  4. Good suggestion for the partition. It is better to keep the small lookup tables as tables, I think. If any column or so is added to the table, the java code generates a new ddl, which I can compare to the database with tools like ERwin. Thank you for your feedback 🙂

  5. This design fallacy goes by the name EAV (Entity-Attribute-Value). It mixes data and meta-data in the schema. You also spit on ISO-11179 data element naming rules with vague, magical, universal names like “type” (what kind of type? blood? customer?) and “id” (identifier of what?). There is no magical, universal identifier or vague generic attributes in RM — “To be is to be something in particular; to be nothing in particular or everything in general is to be nothing.” — Aristotle

    I worked for a company that did this sort of non-design. It falls apart in a about year of production work. By that time, orphaned rows and inconsistent data have accumulated and it is time for the annual reports.

Leave a comment

Your email address will not be published.