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;

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.

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.

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:

BTW, there is no need to use that REFERENCING clause. You can just refer to :new.stagekey etc. in the trigger.

Thank You! It works like a charm!