Delete a column from a table without changing the environment
I'm working on Oracle SQL database, quite big database. One of (among 150 tables) this table has to be changed because it's redundant (it can be generated through a join). I have been asked to delete a column from this table, to get rid of the redundancy. The problem is that now I have to change code everywhere someone made a insert/update/etc on this table (and don't forget the constraint!). I thought "I can make a view that do the right join" so the problem it's solved for all the select, but it's not working for the insert, because I'm updating 2 tables... Is there a way to solve this problem?
My goal is to rename my original table original_table in original_table_smaller (with one less column) and create a view (or something like a view) called original_table that work like the original table.
Is this possible?
As your view will contain one column that is not present in the real table, you will need to use an instead of trigger to make the view updateable.
Something like this:
create table smaller_table ( id integer not null primary key, some_column varchar(20) ); create view real_table as select id, some_column, null as old_column from smaller_table;
Now your old code would run something like this:
insert into real_table (id, some_column, old_column) values (1, 'foo', 'bar');
which results in:
ORA-01733: virtual column not allowed here
To get around this, you need an INSTEAD OF trigger:
create or replace trigger comp_trigger instead of insert on smaller_table begin insert into old_table (id, some_column) values (:new.id, :new.some_column); end; /
Now the value for the "old_column" will be ignored. You need something similar for updates as well.
If your view contains a join, then you can handle that situation as well in the trigger. Simply do an update/insert according to the data to two different tables
For more details and examples, see the manual http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#i1006376