modifying a column

Hello people,
I have a table that I set a column's default to 000. The idea of doing this is that, when that column is left blank during an insert, the 000 will automatically fill that column.

After that altering the table, the developers inserted data into the table using a front end tool, but when we checked the table, the column(that was set to default, 000) that was supposed to be 000 was still empty. Do you have an idea? This is exactly what I did:

SQL>alter table emp modify(
sal number default 000);

SQL>table altered
The idea of doing this is that, when that column is left blank during an insert, the 000 will automatically fill that column.

So, when the developers inserted data from the fron end, and querried the table, the column was still empty instead of that column being 000.
Does anyone have an idea what's going on?

thanks,
charity

P.S sal is the name of the column.
I am running oracle 8i(8.1.5) on unix

[Edited by charity on 12-11-2000 at 08:02 PM]

default will not prevent users to insert NULL.

So since I want the default for that column to be 000, can you please show me an example of how to do that with the column being defaulted to 000?

thanks,
charity

Hmmm - I think that default on a column only works when the column isn't specified in the insert statement - sometimes it's easier to take care of this in the front end code.

hi

it is not possible if u r giving default value
and u r not insearting any record in that column then it will take the default value
so make sure that what the frontend is passing into that column
and one more thing if the datatype of that column is number
and if u r giving 000 it will take only 0
nut if u r having varchar datatype then give like this '000'

Hi guys,
Okay,I think the assumption that I used in my expalnation was not clear cut. It's quite different from the actual scenario. I was thinking that it will be the same.

Okay this is the exact scenario:
I have a table called EMP and has a column called activity_id.
I did a desc on the table and I got this for activity_id

NAME NULL? TYPE
activity_id varchar2(8)

Okay now I want to alter the table so that, the default value for activity_id to be 000, if nothing is entered
in the activity_id column.

I'm sure it's clear now, any help?

Hi,
try this:
CREATE OR REPLACE TRIGGER TI_EMP__ACTIVITY_ID
BEFORE INSERT ON EMP
FOR EACH ROW
BEGIN
IF :NEW.ACTIVITY_ID IS NULL THEN :NEW.ACTIVITY_ID:='000'; END IF;
END TI_EMP__ACTIVITY_ID;

Creating a before insert and update trigger is the better soluction.

For inserting default value , trigger is not a correct solution.
Ensure that the INSERT Statement does not have NULL value for that column.

Hi,
solution with trigger is good for option that u do not need to change the application!

Thanks guys,
I can't use a trigger because the column is not going to be updated. They are new(blanK) columns that new customer will put in thier activity_id. So it's a one time thing, it's not like it will be updated at a later time. The idea is that incase customers forget to put in thier activity_id, then it should default to 000. So i don't think a trigger will work.
To answer tamil's question, I have tried to make the column NULL and NOT NULL but none of them seem to work. Also during the insert I don't specify the column. I have also tried specyfying the column as well but still, it's not working out.
When the front-end that uses power builder tries to insert data into that column, it comes out blank instead of 000

Any more ideas?

From what charity is describing, I am sure that triggers will not help, but I can't think of any way right now? Sorry.

How is the column inseted into the DB?
Is it thru a JAVA or XML application? Like thru a web-site?

- Magnus