We are in the process of reorganizing our database. Changing column names for example.
We're adding the new column name to the existing table and keeping the old name (for now) during the significant code revision process.
So we would have a table:
COL1 NUMBER,
COL2 VARCHAR2(10),
COL1_NEW NUMBER
On insert we need the value from COL1 that's inserted to also be inserted into COL1_NEW.
After all the code is rewritten to use the new COL1_NEW, we plan to drop the original column COL1.
Is it possible to use an insert trigger to copy the value from the current Column (COL1) to the new column (COL1_NEW)?
If so, can you provide an example?
If not, what is your suggestion?
Thank you in advance!
Yes: check whether the original or new column are null, then assign one to the other as appropriate:
begin
if :new.col1_new is null then
:new.col1_new := :new.col1;
else
:new.col1 := :new.col1_new;
end if;
end;
Doing this does have drawbacks though:
- You double your storage requirements for this column
- Doing this in a trigger adds overhead, slowing down DML
- This only works for insert; for UPDATEs you need more advanced checking as you may be setting the column to null
- The values could get out of sync (e.g. because someone disables the trigger); add a check constraint to ensure they're equal to avoid this
You can avoid these by using views and renaming the column in the view definition.
This could either be a view that renames the table:
create or replace view t_new as
select c2, ..., c1 c1_new from t;
When you update the code, you point it to the view and the renamed column.
Or you could:
- Rename the table (e.g. with the same name but in lowercase)
- Create a view with the original table name that selects both the original
rename t to "t";
create or replace view t as
select c1, c2, ..., c1 c1_new from "t";
With either view approach, you're accessing the same column. This avoids problems that can arise from duplication (extra storage, desync, etc.)
Whatever you do it adds complexity.
So it's worth asking: are the current column names so bad that renaming them is worth the cost of updating all the code?