Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unhappy Unanswered: Trigger : Mutating table & Invalid RowID

    Sorry if I'm not all that clear but english is not my first language.

    Context : I need to add a sequence to a table, to finally have a unique primary key (instead of 5 columns). I can not changed the table, because we will have problems with Crystal Reports (fields will not be at the same place). The solution I was given by the analyst was to :
    1. Rename table Inscription_Cours to Inscription.
    2. Add my column INC_SEQ_NO (the sequence in question).
    3. Make a big update in Inscrîption saying INC_SEQ_NO = SEQ_INC.NextVal.
    4. Create a view, give the name Inscription_Cours so that all running software will somewhat be fooled.

    Problem : When there is an insert in Inscription_Cours, I need to make sure that my colum INC_SEQ_NO has a value. I first tried :

    CREATE OR REPLACE TRIGGER MERLIN.IC_Ajout_Seq_NO
    AFTER INSERT ON Inscription
    FOR EACH ROW

    BEGIN

    UPDATE Inscription
    SET INC_SEQ_NO = SEQ_Inc_Seq_No.NextVal
    WHERE INC_FII_SOC_SEQ_NO = :new.INC_FII_SOC_SEQ_NO
    AND INC_FII_SESSION = :new.INC_FII_SESSION
    AND INC_FII_ANNEE = :new.INC_FII_ANNEE
    AND INC_COG_CRS_SEQ_NO = :new.INC_COG_CRS_SEQ_NO
    AND INC_COG_GRO_NO = :new.INC_COG_GRO_NO;
    END;

    It generated the error 'Table is mutating'. After checking the internet, I found out that I can't update a row being inserted. Thus, I tried this :

    INSTEAD OF INSERT ON Inscription_Cours
    FOR EACH ROW

    BEGIN
    INSERT INTO Inscription (Inc_Seq_No, Field1, Field2, etc...)
    VALUES (SEQ_INC_SEQ_NO.NextVal, :New.Field1, :New.Field2, etc..);
    END;

    As you can guess, it didn't work. It said : Ora-01410 : Invalid RowID. Do you guys have any suggestion on how to do it?

    Thanks in advance,

    Francois Labrecque

  2. #2
    Join Date
    Sep 2003
    Posts
    4
    Oh yeah, if you were wondering, we're using Oracle 8i on Window 2000 servers.

    Frank

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    why not create a temporary table with the sequence column.

    create your trigger on insert for that table.

    export your data into a delimited file. Then SQL-load the data into your temp-table. AS sql-loader loads the data, the sequence will generate.

    drop old table, rename new temp table.

    OR

    add your column to existing table, then write a procedure to go through by row-id or whatever and then insert your sequence number, then commit after each update of each row.

    or create a counter like this:
    PHP Code:
    vRecCounter NUMBER := 0;
    begin
    vRecCounter 
    := 0;

    loop
    vRecCounter 
    := vRecCounter 1;
    If 
    Mod(vRecCountervCommitPoint) = 0 Then
                Commit
    ;
    Dbms_Output.Put_LineTo_Char(SysDate'HH24:MI:SS') || 'Commit Reached.  rows = (' || To_Char(vRecCounter) || ')' );
    End If;
    end loop


    etc
    etc
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Hi

    Hi

    I am not sure if your problem was solved or not, but I must say you have a problem that everyone using older systems, with not much re-thinking happening every now and then would have.

    I have 4 questions for you, and would appreciate your reply with some description.
    1. You have an application that is doing the inserts in the oracle DB. Also you have the crystal reports which uses the 5 columns to insert data. Please confirm.
    2. if you have to create a primary key, what is the reason to have a different primary key. If performance is the issue, then adding a primary key, and still be using the same old 5 columns, performance wise it would be the same.
    3. How are the inserts happening into the table right now.
    4. It seems that someone created Crystal Report for you who no longer works for you, and that why u fear if it would break the report. Please confirm.


    Regards
    Aruneesh

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Trigger : Mutating table & Invalid RowID

    Originally posted by brouminator
    Sorry if I'm not all that clear but english is not my first language.

    Context : I need to add a sequence to a table, to finally have a unique primary key (instead of 5 columns). I can not changed the table, because we will have problems with Crystal Reports (fields will not be at the same place). The solution I was given by the analyst was to :
    1. Rename table Inscription_Cours to Inscription.
    2. Add my column INC_SEQ_NO (the sequence in question).
    3. Make a big update in Inscrîption saying INC_SEQ_NO = SEQ_INC.NextVal.
    4. Create a view, give the name Inscription_Cours so that all running software will somewhat be fooled.

    Problem : When there is an insert in Inscription_Cours, I need to make sure that my colum INC_SEQ_NO has a value. I first tried :

    CREATE OR REPLACE TRIGGER MERLIN.IC_Ajout_Seq_NO
    AFTER INSERT ON Inscription
    FOR EACH ROW

    BEGIN

    UPDATE Inscription
    SET INC_SEQ_NO = SEQ_Inc_Seq_No.NextVal
    WHERE INC_FII_SOC_SEQ_NO = :new.INC_FII_SOC_SEQ_NO
    AND INC_FII_SESSION = :new.INC_FII_SESSION
    AND INC_FII_ANNEE = :new.INC_FII_ANNEE
    AND INC_COG_CRS_SEQ_NO = :new.INC_COG_CRS_SEQ_NO
    AND INC_COG_GRO_NO = :new.INC_COG_GRO_NO;
    END;

    It generated the error 'Table is mutating'. After checking the internet, I found out that I can't update a row being inserted. Thus, I tried this :

    INSTEAD OF INSERT ON Inscription_Cours
    FOR EACH ROW

    BEGIN
    INSERT INTO Inscription (Inc_Seq_No, Field1, Field2, etc...)
    VALUES (SEQ_INC_SEQ_NO.NextVal, :New.Field1, :New.Field2, etc..);
    END;

    As you can guess, it didn't work. It said : Ora-01410 : Invalid RowID. Do you guys have any suggestion on how to do it?

    Thanks in advance,

    Francois Labrecque
    Your trigger should not contain an UPDATE statement. It should be:

    CREATE OR REPLACE TRIGGER MERLIN.IC_Ajout_Seq_NO
    BEFORE INSERT ON Inscription
    FOR EACH ROW

    BEGIN

    SELECT SEQ_Inc_Seq_No.NextVal
    INTO :new.INC_SEQ_NO
    FROM DUAL;

    END;

    Note that it is a BEFORE insert trigger, because it changes one of the new values before it is inserted into the table.

    If this new surrogate key replaces your previous 5-column primary key, I hope you will be adding a UNIQUE constraint on those 5 columns?

Posting Permissions

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