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?

Answers


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


Need Your Help

MySQL inner join query with 2 tables

mysql sql join inner-join

I'm used to do MySQL querys only with one table. Now I need to connect two and have no idea :/

IOS:Swift: Video Screen Capture

ios ipad swift video video-capture

What is the screen device called in iOS/Swift?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.