modifying a column
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: modifying a column

  1. #1
    Join Date
    Aug 2000
    Location
    NY
    Posts
    37

    Unhappy inserting data into table, help??

    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]

  2. #2
    Join Date
    Jun 2000
    Posts
    295
    default will not prevent users to insert NULL.

  3. #3
    Join Date
    Aug 2000
    Location
    NY
    Posts
    37
    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

  4. #4
    Join Date
    Dec 2000
    Posts
    11
    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.

  5. #5
    Join Date
    Sep 2000
    Posts
    305

    check this

    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'

  6. #6
    Join Date
    Aug 2000
    Location
    NY
    Posts
    37

    Lightbulb

    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?

  7. #7
    Join Date
    Dec 2000
    Posts
    11

    Thumbs up

    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;

  8. #8
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Creating a before insert and update trigger is the better soluction.

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    For inserting default value , trigger is not a correct solution.
    Ensure that the INSERT Statement does not have NULL value for that column.

  10. #10
    Join Date
    Dec 2000
    Posts
    11

    Thumbs up

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width