Re: Problems with DEFAULT column

Robert Hale wrote: > > I'm trying to ensure that a field in a table which is restricted to > being either 0 or 1 is never left with a NULL value. However, the > default assignment on the appropriate attribute. Even if I have > specified a default value for the attribute, other applications, such as > forms and some generated ESQL insert statements always seem to ensure > that the field has a NULL value. > > For example, I have altered the table with the following command: > alter table historic_data> modify fault_view integer default 0 > > If I attempt to datafill the table using the Informix LOAD command and > leave the fault_view field as NULL in the data file, then each record > created during the load will have a NULL value in fault_view. The > desired behaviour would be to have fault_view set to 0 even if the LOAD > caommand is used to data fill the table. > > Does anyone know the syntax for a trigger that would reset the value > of the fault_view field BEFORE it was inserted into the table? Is a > trigger the answer or is there a better mechanism? it is very missleading the default setting of a column. the one way I have seen it work properly is when you actually do an explicit insert. for example, if your table historic_data is set up in this way historic_data ( col1 char(1), col2 char(1), col3 char(1), fault_view integer default 0 ); you could do: insert into historic_data (col2) values ( "A") this would create a new record for the table and any constraints defined would then be applied since the column name is not referenced directly. -- Jorge Torralba Intel Information Technology HF2-71 (503)696-4587 5200 NE Elam Young Parkwa jorge_torralba@ccm2.hf.intel.com Hillsboro, Or 97124 ============================================================= Any views or opinions expressed by me do not reflect those of Intel Corp.