CODEWITHSUNDEEP
oracle10g
kalls
kalls

Reputation: 2855

Oracle Default value on Number column

I have a number column on a table called SampleTable which is defined as follows

ID Number(10) DEFAULT 1

I have a trigger on different table(SomeTable) and UPDATE EACH ROW (Trigger - :New.ID) which inserts value from that table(SomeTable) into the SampleTable.

Sometimes the ID from SomeTable can be NULL and I wanted to SampleTable to insert the default value 1 in my case.

But it inserts NULL(Blankspace) on the table.

I am kinda new to PL/SQL so any help is greatly appreciated.

Upvotes: 2

Views: 11096

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132570

A column default is only used if the column is not specified in the insert:

insert into sampletable (othercol) values ('x'); -- ID will default to 1 here

It is not used if the column has been specified, even if a NULL has been passed in:

insert into sampletable (othercol, id)
values ('x', null); -- ID will be set to null here

To deal with this, your trigger can do this:

insert into sampletable (othercol, id)
values ('x', coalesce(:new.id,1));

Upvotes: 2

Related Questions