create table with default value that combines two columns

Question

Is it possible to create a table with a column that combines two column values? something like this:

create table test1 ( number1 decimal(6,2), number2 decimal(6,2), total decimal(6,2) DEFAULT (number1+number2) );

Answer 1

You need to use computed columns.

Answer 2

Yes, in 11G. It is called a "virtual" column. The syntax is:

create table test1
 ( number1 number(6,2),
   number2 number(6,2),
   total number(6,2) generated always as (number1+number2) );

(There isn't a DECIMAL datatype in Oracle AFAIK, so I used NUMBER instead.)

See also: documentation

NB The "generated always" keywords are optional.

The types of expression allowed after "as" are described here.

Answer 3

You can do this using a trigger:

create table test1
  (number1 decimal(6,2),
   number2 decimal(6,2),
   total decimal(6,2));

CREATE TRIGGER test1_bi
  BEFORE INSERT ON test1
  FOR EACH ROW
BEGIN
  IF :new.total is NULL THEN
    :NEW.TOTAL := :new.number1 + :new.number2;
  END IF;
END test1_bi;

INSERT INTO test1(number1, number2) VALUES(1, 2);

SELECT * FROM test1;

After the SELECT you'll find that TOTAL has a value of 3, as expected.

Share and enjoy.