Re: Question about insert and defaut values

Hi,

Your solution works, but it's very complex, because I would need to alter many tables. In fact, many insert are automatically generated and I could change the defaults without changing my code.

The approach

INSERT INTO TABLE ('Any Value', default, default )

is the easiest because the keyword 'default' does the work. No triggers at all. Is it possible in Oracle?

I know the positional approach is not the preferred, but I would solve my problems faster if there is a keyword like 'default'.

Our migration from SQL Server to Oracle is missing just this topic.

Thanks again,

On Sun, 21 Mar 1999 17:23:53 +0100, "Arjan van Bentem" <avbentem_at_DONT-YOU-DAREdds.nl> wrote:

>Eugenio Reis wrote
>> I'm insisting on a positional approach because many Insert statements
>> I had on SQL Server (a VB program) use that approach and I have no
>> time to specify columns. I tested:
>>
>> INSERT INTO TABLE
>> VALUES ( 'AnyName', , 'AnyValue' )
>
>This indeed is not the same. You should really not mention the column when you
>want Oracle to use the default. I guess you need to write a simple trigger
>that changes NULL (or any keyword you like) into the default. Note that this
>is not the same behavior as a table level default. In the latter, when you
>insert NULL into a column that has a default, NULL is still inserted, not the
>default. In this trigger either specifying NULL or nothing at all both yield
>the default.:
>
> create or replace trigger bi_mytable
> before insert on my mytable
> for each row
> begin
> :new.my_column =nvl(:new.my_column, 'my default value');
> end bi_mytable;
> /
> show errors
>
>If you insist on taking the default out of the table definition instead of
>hardcoding it in the trigger, then you could use something like:
>
> if :new.my_column is null then
> select data_default
> into :new.my_column
> from user_tab_columns
> where table_name = 'MY_TABLE'
> and column_name = 'MY_COLUMN';
> end if;
>
>Not too nice, in my opinion, but it may save you some redesign time.
>
>> In SQL Server I could write
>> INSERT INTO TABLE VALUES ( 'Any name', default )
>
>So, maybe you can change your code and global replace the word default with
>the word null.
>
>By the way: note that positional approach is not to be preferred either. When
>you add a column (using alter table, the column will be added as the last
>column), or change the installation scripts (where you might put the new
>column in another position), the column order in two databases may be
>different!
>
>Arjan.
>
>

Eugenio Reis
** Please remove the prime numbers to get my real e-mail Received on Mon Mar 22 1999 - 07:04:05 CST