Category Archives: data modeling

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.