Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Post Unanswered: mutating trigger issue...need help

    When a record is updated in kb_errortransaction I want it inserted in to kb_stagetransaction. In the insert I also update\null 3 fields. After this update I want to delete the record from kb_errortransaction but...the delete (at the bottom) is causing problems...mutating table error.

    I'm new to Oracle and triggers so explain this as if I know nothing!


    CREATE OR REPLACE TRIGGER ERROR_CORRECTION
    after update on kb_errortransaction
    REFERENCING NEW AS newRow
    FOR EACH ROW
    begin
    /*INSERT INTO KB_STAGEKEY
    SELECT STAGEKEY FROM KB_ERRORTRANSACTION; */

    Insert Into Kb_StageTransaction
    (STAGEKEY,
    BATCHNAME,
    ORDERID,
    LINENUMBER,
    SUBLINENUMBER,
    EVENTTYPE,
    ACCOUNTINGDATE,
    PRODUCTID,
    PRODUCTNAME,
    PRODUCTDESCRIPTION,
    VALUE,
    UNITOFMEASURE,
    NUMBEROFUNITS,
    UNITVALUE,
    ACTIONTYPE,
    COMPENSATIONDATE,
    PAYMENTTERMS,
    PONUMBER,
    CHANNEL,
    ALTERNATEORDERNUMBER,
    DATASOURCE,
    NATIVECURRENCY,
    NATIVECURRENCYAMOUNT,
    DISCOUNTPERCENT,
    DISCOUNTTYPE,
    BILLTOCUSTID,
    BILLTOCONTACT,
    BILLTOCOMPANY,
    BILLTOAREACODE,
    BILLTOPHONE,
    BILLTOFAX,
    BILLTOADDRESS1,
    BILLTOADDRESS2,
    BILLTOADDRESS3,
    BILLTOCITY,
    BILLTOSTATE,
    BILLTOCOUNTRY,
    BILLTOPOSTALCODE,
    BILLTOINDUSTRY,
    BILLTOGEOGRAPHY,
    SHIPTOCUSTID,
    SHIPTOCONTACT,
    SHIPTOCOMPANY,
    SHIPTOAREACODE,
    SHIPTOPHONE,
    SHIPTOFAX,
    SHIPTOADDRESS1,
    SHIPTOADDRESS2,
    SHIPTOADDRESS3,
    SHIPTOCITY,
    SHIPTOSTATE,
    SHIPTOCOUNTRY,
    SHIPTOPOSTALCODE,
    SHIPTOINDUSTRY,
    SHIPTOGEOGRAPHY,
    OTHERTOCUSTID,
    OTHERTOCONTACT,
    OTHERTOCOMPANY,
    OTHERTOAREACODE,
    OTHERTOPHONE,
    OTHERTOFAX,
    OTHERTOADDRESS1,
    OTHERTOADDRESS2,
    OTHERTOADDRESS3,
    OTHERTOCITY,
    OTHERTOSTATE,
    OTHERTOCOUNTRY,
    OTHERTOPOSTALCODE,
    OTHERTOINDUSTRY,
    OTHERTOGEOGRAPHY,
    REASONID,
    GENERICATTRIBUTE1,
    GENERICATTRIBUTE2,
    GENERICATTRIBUTE3,
    GENERICATTRIBUTE4,
    GENERICATTRIBUTE5,
    GENERICATTRIBUTE6,
    GENERICATTRIBUTE7,
    GENERICATTRIBUTE8,
    GENERICATTRIBUTE9,
    GENERICATTRIBUTE10,
    GENERICATTRIBUTE11,
    GENERICATTRIBUTE12,
    GENERICATTRIBUTE13,
    GENERICATTRIBUTE14,
    GENERICATTRIBUTE15,
    GENERICATTRIBUTE16,
    GENERICATTRIBUTE17,
    GENERICATTRIBUTE18,
    GENERICATTRIBUTE19,
    GENERICATTRIBUTE20,
    GENERICATTRIBUTE21,
    GENERICATTRIBUTE22,
    GENERICATTRIBUTE23,
    GENERICATTRIBUTE24,
    GENERICATTRIBUTE25,
    GENERICATTRIBUTE26,
    GENERICATTRIBUTE27,
    GENERICATTRIBUTE28,
    GENERICATTRIBUTE29,
    GENERICATTRIBUTE30,
    GENERICATTRIBUTE31,
    GENERICATTRIBUTE32,
    GENERICNUMBER1,
    GENERICNUMBER2,
    GENERICNUMBER3,
    GENERICNUMBER4,
    GENERICNUMBER5,
    GENERICNUMBER6,
    GENERICDATE1,
    GENERICDATE2,
    GENERICDATE3,
    GENERICDATE4,
    GENERICDATE5,
    GENERICDATE6,
    GENERICBOOLEAN1,
    GENERICBOOLEAN2,
    GENERICBOOLEAN3,
    GENERICBOOLEAN4,
    GENERICBOOLEAN5,
    GENERICBOOLEAN6,
    ECA1PARTICIPANTID,
    ECA1POSITIONNAME,
    ECA1POSITIONTYPEID,
    ECA2PARTICIPANTID,
    ECA2POSITIONNAME,
    ECA2POSITIONTYPEID,
    ECA3PARTICIPANTID,
    ECA3POSITIONNAME,
    ECA3POSITIONTYPEID,
    ECA4PARTICIPANTID,
    ECA4POSITIONNAME,
    ECA4POSITIONTYPEID,
    BUSINESSUNITNAME,
    STAGEPROCESSDATE,
    STAGEPROCESSFLAG,
    STAGEVALIDTRANSACTIONSEQ,
    STAGEBUSINESSUNITSEQ)

    VALUES (
    :newrow.STAGEKEY,
    :newrow.BATCHNAME,
    :newrow.ORDERID,
    :newrow.LINENUMBER,
    :newrow.SUBLINENUMBER,
    :newrow.EVENTTYPE,
    :newrow.ACCOUNTINGDATE,
    :newrow.PRODUCTID,
    :newrow.PRODUCTNAME,
    :newrow.PRODUCTDESCRIPTION,
    :newrow.VALUE,
    :newrow.UNITOFMEASURE,
    :newrow.NUMBEROFUNITS,
    :newrow.UNITVALUE,
    :newrow.ACTIONTYPE,
    :newrow.COMPENSATIONDATE,
    :newrow.PAYMENTTERMS,
    :newrow.PONUMBER,
    :newrow.CHANNEL,
    :newrow.ALTERNATEORDERNUMBER,
    :newrow.DATASOURCE,
    :newrow.NATIVECURRENCY,
    :newrow.NATIVECURRENCYAMOUNT,
    :newrow.DISCOUNTPERCENT,
    :newrow.DISCOUNTTYPE,
    :newrow.BILLTOCUSTID,
    :newrow.BILLTOCONTACT,
    :newrow.BILLTOCOMPANY,
    :newrow.BILLTOAREACODE,
    :newrow.BILLTOPHONE,
    :newrow.BILLTOFAX,
    :newrow.BILLTOADDRESS1,
    :newrow.BILLTOADDRESS2,
    :newrow.BILLTOADDRESS3,
    :newrow.BILLTOCITY,
    :newrow.BILLTOSTATE,
    :newrow.BILLTOCOUNTRY,
    :newrow.BILLTOPOSTALCODE,
    :newrow.BILLTOINDUSTRY,
    :newrow.BILLTOGEOGRAPHY,
    :newrow.SHIPTOCUSTID,
    :newrow.SHIPTOCONTACT,
    :newrow.SHIPTOCOMPANY,
    :newrow.SHIPTOAREACODE,
    :newrow.SHIPTOPHONE,
    :newrow.SHIPTOFAX,
    :newrow.SHIPTOADDRESS1,
    :newrow.SHIPTOADDRESS2,
    :newrow.SHIPTOADDRESS3,
    :newrow.SHIPTOCITY,
    :newrow.SHIPTOSTATE,
    :newrow.SHIPTOCOUNTRY,
    :newrow.SHIPTOPOSTALCODE,
    :newrow.SHIPTOINDUSTRY,
    :newrow.SHIPTOGEOGRAPHY,
    :newrow.OTHERTOCUSTID,
    :newrow.OTHERTOCONTACT,
    :newrow.OTHERTOCOMPANY,
    :newrow.OTHERTOAREACODE,
    :newrow.OTHERTOPHONE,
    :newrow.OTHERTOFAX,
    :newrow.OTHERTOADDRESS1,
    :newrow.OTHERTOADDRESS2,
    :newrow.OTHERTOADDRESS3,
    :newrow.OTHERTOCITY,
    :newrow.OTHERTOSTATE,
    :newrow.OTHERTOCOUNTRY,
    :newrow.OTHERTOPOSTALCODE,
    :newrow.OTHERTOINDUSTRY,
    :newrow.OTHERTOGEOGRAPHY,
    Null,
    :newrow.GENERICATTRIBUTE1,
    :newrow.GENERICATTRIBUTE2,
    :newrow.GENERICATTRIBUTE3,
    :newrow.GENERICATTRIBUTE4,
    :newrow.GENERICATTRIBUTE5,
    :newrow.GENERICATTRIBUTE6,
    :newrow.GENERICATTRIBUTE7,
    :newrow.GENERICATTRIBUTE8,
    :newrow.GENERICATTRIBUTE9,
    :newrow.GENERICATTRIBUTE10,
    :newrow.GENERICATTRIBUTE11,
    :newrow.GENERICATTRIBUTE12,
    :newrow.GENERICATTRIBUTE13,
    :newrow.GENERICATTRIBUTE14,
    :newrow.GENERICATTRIBUTE15,
    :newrow.GENERICATTRIBUTE16,
    :newrow.GENERICATTRIBUTE17,
    :newrow.GENERICATTRIBUTE18,
    :newrow.GENERICATTRIBUTE19,
    :newrow.GENERICATTRIBUTE20,
    :newrow.GENERICATTRIBUTE21,
    :newrow.GENERICATTRIBUTE22,
    :newrow.GENERICATTRIBUTE23,
    :newrow.GENERICATTRIBUTE24,
    :newrow.GENERICATTRIBUTE25,
    :newrow.GENERICATTRIBUTE26,
    :newrow.GENERICATTRIBUTE27,
    :newrow.GENERICATTRIBUTE28,
    :newrow.GENERICATTRIBUTE29,
    :newrow.GENERICATTRIBUTE30,
    :newrow.GENERICATTRIBUTE31,
    :newrow.GENERICATTRIBUTE32,
    :newrow.GENERICNUMBER1,
    :newrow.GENERICNUMBER2,
    :newrow.GENERICNUMBER3,
    :newrow.GENERICNUMBER4,
    :newrow.GENERICNUMBER5,
    :newrow.GENERICNUMBER6,
    :newrow.GENERICDATE1,
    :newrow.GENERICDATE2,
    :newrow.GENERICDATE3,
    null,
    trunc(sysdate),
    :newrow.GENERICDATE6,
    :newrow.GENERICBOOLEAN1,
    :newrow.GENERICBOOLEAN2,
    :newrow.GENERICBOOLEAN3,
    :newrow.GENERICBOOLEAN4,
    '1',
    :newrow.GENERICBOOLEAN6,
    :newrow.ECA1PARTICIPANTID,
    :newrow.ECA1POSITIONNAME,
    :newrow.ECA1POSITIONTYPEID,
    :newrow.ECA2PARTICIPANTID,
    :newrow.ECA2POSITIONNAME,
    :newrow.ECA2POSITIONTYPEID,
    :newrow.ECA3PARTICIPANTID,
    :newrow.ECA3POSITIONNAME,
    :newrow.ECA3POSITIONTYPEID,
    :newrow.ECA4PARTICIPANTID,
    :newrow.ECA4POSITIONNAME,
    :newrow.ECA4POSITIONTYPEID,
    :newrow.BUSINESSUNITNAME,
    :newrow.STAGEPROCESSDATE,
    :newrow.STAGEPROCESSFLAG,
    :newrow.STAGEVALIDTRANSACTIONSEQ,
    :newrow.STAGEBUSINESSUNITSEQ);

    /* delete kb_errortransaction where stagekey= :newrow.stagekey; */


    end;

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    HI,

    The mutating table problem comes when the trigger tries to select or modify any row of the same table. This situation comes when a table preserves some effective date.
    SATHISH .

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table the trigger is triggering from.

    If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
    SATHISH .

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by satish_ct
    If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
    I don't agree with that. There are other ways to work around the mutating table issue, without sacraficing declarative referential constraints.

    The problem in this case is that the trigger wants to delete the very record that was updated to fire the trigger:

    CREATE OR REPLACE TRIGGER ERROR_CORRECTION
    after update on kb_errortransaction
    REFERENCING NEW AS newRow
    FOR EACH ROW
    begin
    ...
    delete kb_errortransaction where stagekey= :newrow.stagekey;
    end;

    The standard work around is to defer the problem code until an AFTER trigger at statement level (i.e. not FOR EACH ROW), using a packaged collection to store details of the records to be deleted:

    Code:
    create or replace package pkg is
      type stagekey_tab_type is table of kb_errortransaction.stagekey%type index by binary_integer;
      stagekey_tab stagekey_tab_type;
    end;
    /
    
    CREATE OR REPLACE TRIGGER ERROR_CORRECTION
    after update on kb_errortransaction
    REFERENCING NEW AS newRow
    FOR EACH ROW
    begin
      -- Do everything except the problem delete
      ...
      -- Do this instead of the delete
      pkg.stagekey_tab( stagekey_tab.count+1 ) := :newrow.stagekey;
    end;
    /
    
    CREATE OR REPLACE TRIGGER ERROR_CORRECTION2
    after update on kb_errortransaction
    REFERENCING NEW AS newRow
    -- Note: no FOR EACH ROW here!
    begin
      -- Delete all the rows
      forall i in 1..pkg.stagekey_tab.count
        delete kb_errortransaction where stagekey= pkg.stagekey_tab(i);
      -- Clear the table for next time
      pkg.stagekey_tab.delete;
    end;
    /
    BTW, there is no need to use that REFERENCING clause. You can just refer to :new.stagekey etc. in the trigger.

  5. #5
    Join Date
    Jan 2004
    Posts
    5

    mutating table

    Thank You! It works like a charm!

Posting Permissions

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