Copying fields in Oracle

Question

Hi, I am not a programmer, but have a task of automatically copying one field in a table to another field in the same table (it's a long story... :-) ). This should be done on update and insert and I really do not know how to go about it. I should point out that data is entered to the DB through a user-interface which we do not have the source code for, and therefore we want to do this change on a DB level, using a trigger or likes. I have tried creating a simple trigger that will copy the values across, but came up with an error message. After Googling the error, I found that I need to create a package which will be used as a variable. Now I am really lost!!!! :-)

I want to also point out that I need a solution that will update this field automatically from now on, but not override any data that already exists in the column.

Could someone show me the easiest and simplest way of doing this entire procedure? I really need a 'Guide for dummies' approach.

Thanks, David

Answer 1

A simple trigger will be adequate if both fields are on the same table.

Consider:

SQL> CREATE TABLE t (ID NUMBER, source_col VARCHAR2(10), dest_col VARCHAR2(10));

Table created
SQL> CREATE OR REPLACE TRIGGER trg_t
  2     BEFORE INSERT OR UPDATE OF source_col ON t
  3     FOR EACH ROW
  4  BEGIN
  5     IF :old.dest_col IS NULL THEN
  6        :NEW.dest_col := :NEW.source_col;
  7     END IF;
  8  END;
  9  /

Trigger created

We check if the trigger works for insert then update (the value we inserted will be preserved):

SQL> INSERT INTO t(ID, source_col) VALUES (1, 'a');

1 row inserted
SQL> SELECT * FROM t;

        ID SOURCE_COL DEST_COL
---------- ---------- ----------
         1 a          a
SQL> UPDATE t SET source_col = 'b';

1 row updated
SQL> SELECT * FROM t;

        ID SOURCE_COL DEST_COL
---------- ---------- ----------
         1 b          a

Edit: I updated the trigger to take into account the requirement that the existing data on dest_col is to be preserved.

Answer 2

Thanks guys. Worked like a gem. Cheers, David

Answer 3

If you just need the new column to show the exact same data as the old column I think (if you're using Oracle 11g) that you can create a virtual column.

There's an example here.