Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2006
    Posts
    10

    Unanswered: ORA-01407: cannot update ("MY_TABLE"."BA") to NULL

    if I have a table defined like this ...
    ************************************************** ***********************
    Name Null? Type
    ----------------------------------------------------- --------
    ------------------------------------
    PEC NOT NULL
    VARCHAR2(6)
    BA NOT NULL
    VARCHAR2(8)
    SUB
    VARCHAR2(10)
    S
    VARCHAR2(3)
    FOATITLE
    ************************************************** **************************
    and a trigger like this....
    ************************************************** **************************
    CREATE OR REPLACE TRIGGER TRG_PEC_MY_TABLE
    BEFORE INSERT OR UPDATE
    ON MY_TABLE
    FOR EACH ROW
    /**
    * This trigger updates the BA field this table.
    */
    BEGIN
    --
    :NEW.BA := fnc_cb_get_pec_ba(:NEW.PEC); <-- just retrieves a ba
    value for a given pec value

    END;
    /
    ************************************************** ****************************

    if I try to alter the table like this ...
    ************************************************** ****************************
    ALTER TABLE my_table ADD (
    oac VARCHAR2(2) DEFAULT '47' NOT NULL,
    CONSTRAINT MY_TABLE_OAC_OAC_FK FOREIGN KEY (OAC)
    REFERENCES command_codes(oac)
    );
    ************************************************** ****************************

    I get this error ...

    ************************************************** ****************************
    ALTER TABLE my_table ADD (
    *
    ERROR at line 1:
    ORA-01407: cannot update ("MY_TABLE"."BA") to NULL
    ************************************************** ****************************

    but if I disable the trigger and then run the above alter statement, it
    works.... also if I take out the DEFAULT '47' part of the alter
    statement , it works also... any ideas?

    Thanks

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    When you issue the alter table statement, an update is performed to set the new (OAC) column to 47 in the existing rows in the table. This will fire the trigger which in turn calls the function.
    I suspect your function returns null whenever a value which already exists in the PEC column is passed to it. This would not happen if the trigger is BEFORE INSERT, rather than BEFORE INSERT OR UPDATE. Or the code is changed to handle updates appropriately.

    .

Posting Permissions

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