Skip to Main Content
  • Questions
  • Copy value from one column to another on insert using Insert Trigger

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Theresa.

Asked: January 13, 2024 - 12:32 am UTC

Last updated: January 16, 2024 - 3:07 pm UTC

Version: Version 19.19.0.0.0

Viewed 1000+ times

You Asked

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!

and Chris said...

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?

Rating

  (1 rating)

Comments

Thank you!

Theresa, January 16, 2024 - 2:41 pm UTC

Thank you so much for the response AND for validating my reasoning for taking this approach!

Unfortunately, we do need to rename the columns, per requirements. We'll ultimately be renaming the tables as well.

I was hoping to use the SQL Object Type to do all this so I could hide the table name and the column name, but that lead to severe performance issues unless I passed several parameters to the function and created more complex logic to build a dynamic where clause. Alternately, I thought of creating a separate function for each use case, to avoid the dynamic where clause, but that too seemed to be a lot of additional work for an "interim" solution.

So, in the interim phase of this project, additional columns and the trigger seems to be the best solution. Hopefully we can live with any performance issues until we get everything moved.

Chris Saxon
January 16, 2024 - 3:07 pm UTC

You're welcome.

I'd recommend looking into the view approaches further - unlike an object-based approach there will be no performance overhead to doing this.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library