How to set a conditional DEFAULT value to a column

Question

How to set a conditional DEFAULT value to a column in oracle without using triggers? I want to achieve following needs:

1. If "flag1"=1 then Default value to column Newfield must be "4".
2. If "flag1"=2 then Default value to column Newfield must be "5".

Answer 1

If 'flag' is something session (or statement) level then SYS_CONTEXT may be a way through. Possibly a BEFORE STATEMENT trigger if the issue is avoiding a context switch for every row inserted

Answer 2

If column Newfield does not need to be updateable, then you could simply implement it in a view, as Tony showed, or in 11g you could make it a virtual column.

Answer 3

That's what triggers are for. Use them.

Answer 4

A column DEFAULT is not allowed to reference another column, so this is not possible.

You could perhaps just let the default remain as NULL, and then have a view to adjust it like:

create view mytable_view as
select flag1, nvl(newfield, case flag1 when 1 then 4
                                       when 2 then 5
                            end) as newfield
from mytable;