Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Question about insert and defaut values
Eugenio Reis wrote
> 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?
No.
If you do not want to change your code, then at least you need the trigger. You could create a script that creates a trigger for each table that has columns with a default value. When the triggers are created, all you need to do is global replace DEFAULT with NULL. Or, define a constant DEFAULT in all your scripts, if Oracle does not think it is a reserved word (which I think it is). You do not even have to assign a value to it (it defaults to NULL anyway).
Maybe you can create a package, define a constant DEFAULT in the package, and also define a public synonym DEFAULT for MY_PACKAGE.DEFAULT (if you try that, let us know if you succeeded). However, I would prefer the NULL, as DEFAULT could make someone think that it is a Oracle keyword -- if it is not already...
Again, triggers that check if the value is NULL do not behave exactly like a normal Oracle default would.
Below is some code I once used and editted a bit for you. It has not been tested. Furthermore, I do not know how date type defaults are stored, so you might need to change the code to handle dates correctly.
Arjan.
prompt Creating temporary script to generate before insert triggers
set termout off
spool CreateTriggers.tmp
prompt -- This is a generated script. It may be deleted.
declare
cursor cTables is
select table_name
from user_tab_columns
where data_default is not null;
--
cursor cColumns( pTableName varchar2 ) is
select tab.column_name, tab.data_type, tab.data_default
from user_tab_columns tab
where tab.table_name = pTableName;
--
procedure WriteLn( pLine in varchar2 ) is
begin
dbms_output.put_line( pLine );
end WriteLn;
--
begin
for rTable in cTables
loop
WriteLn( 'end bi_' || rTable.table_name || ';' );
WriteLn( '/' );
end loop;
end;
/
spool off
set termout on
set feedback on
spool CreateTriggers.log
prompt Executing temporary script CreateTriggers.tmp
start CreateTriggers.tmp
spool off
prompt Completed temporary script CreateTriggers.tmp
You might want to use
loop
WriteLn( '-- assign default value if NULL is entered' ); WriteLn( 'if :new.' || rColumn.column_name || ' is null then' ); WriteLn( 'select data_default' ); WriteLn( 'into :new.' || rColumn.column_name ); WriteLn( 'from user_tab_columns' ); WriteLn( 'where table_name = ''' || rTable.table_name || '''' ); WriteLn( 'and column_name = ''' || rColumn.column_name || ''';' ); WriteLn( 'end if;' );
You might also want to delete the 'or replace' part to avoid overwriting existing triggers.
Arjan. Received on Mon Mar 22 1999 - 11:45:37 CST
![]() |
![]() |