CODEWITHSUNDEEP
oracle-database
Jay
Jay

Reputation: 85

create table with default value that combines two columns

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) );

Upvotes: 2

Views: 4098

Answers (3)

Tony Andrews
Tony Andrews

Reputation: 132570

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.

Upvotes: 5

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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.

Upvotes: 0

Danil
Danil

Reputation: 1893

You need to use computed columns.

Upvotes: 0

Related Questions