Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008
    Posts
    2

    Unanswered: Mutating table - trigger

    Hi all.

    I am trying to get around the mutating trigger error when having a trigger on a table that updates the same table.

    What I have done so far is:
    1. create a package that will store the :new values.
    2. I then use a BEFORE trigger to populate these values.
    3. I then have an after trigger that calls a stored procedure which updates the table.

    Note that the tringger is on columns address1,address2,..address7. The procedure does not update these columns, and therefore does not re-activate the trigger.

    Here is the code so far. I thought this was the way to avoid mutating triggers. I must have missed something.

    Hope someone can point out the mistake.

    Thanks a lot
    superkav

    create or replace package postcode_update_pkg
    as type address_type is table of tocpostaladdress.address1%type
    index by binary_integer;
    pcval tocpostaladdress.c%type;
    pival tocpostaladdress.i%type;
    newaddress1 tocpostaladdress.address1%type;
    newaddress2 tocpostaladdress.address2%type;
    newaddress3 tocpostaladdress.address3%type;
    newaddress4 tocpostaladdress.address4%type;
    newaddress5 tocpostaladdress.address5%type;
    newaddress6 tocpostaladdress.address6%type;
    newaddress7 tocpostaladdress.address7%type;

    end;
    /

    create or replace trigger postaladdress_before
    before insert or update
    OF ADDRESS1 ,ADDRESS2 ,ADDRESS3 ,ADDRESS4 ,ADDRESS5 ,ADDRESS6 ,ADDRESS7
    on tocpostaladdress
    for each row
    declare
    tpcval tocpostaladdress.c%type := :new.c;
    tpival tocpostaladdress.i%type := :new.i;
    tnewaddress1 tocpostaladdress.address1%type := :new.address1;
    tnewaddress2 tocpostaladdress.address2%type := :new.address2;
    tnewaddress3 tocpostaladdress.address3%type := :new.address3;
    tnewaddress4 tocpostaladdress.address4%type := :new.address4;
    tnewaddress5 tocpostaladdress.address5%type := :new.address5;
    tnewaddress6 tocpostaladdress.address6%type := :new.address6;
    tnewaddress7 tocpostaladdress.address7%type := :new.address7;

    begin

    postcode_update_pkg.pcval := tpcval;
    postcode_update_pkg.pival := tpival;
    postcode_update_pkg.newaddress1 := tnewaddress1;
    postcode_update_pkg.newaddress2 := tnewaddress2;
    postcode_update_pkg.newaddress3 := tnewaddress3;
    postcode_update_pkg.newaddress4 := tnewaddress4;
    postcode_update_pkg.newaddress5 := tnewaddress5;
    postcode_update_pkg.newaddress6 := tnewaddress6;
    postcode_update_pkg.newaddress7 := tnewaddress7;
    end;
    / create or replace trigger postaladdress_after
    after insert or update
    OF ADDRESS1 ,ADDRESS2 ,ADDRESS3 ,ADDRESS4 ,ADDRESS5 ,ADDRESS6 ,ADDRESS7
    on tocpostaladdress
    for each row
    begin

    update_postcode(postcode_update_pkg.pcval,postcode _update_pkg.pival,postcode_update_pkg.newaddress1, postcode_update_pkg.newaddress2,postcode_update_pk g.newaddress3,postcode_update_pkg.newaddress4,post code_update_pkg.newaddress5,postcode_update_pkg.ne waddress6,postcode_update_pkg.newaddress7) ;
    end;
    /

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

    1) The package needs to maintain collections (arrays) of values for each row that triggered the trigger.

    2) The final trigger should not have FOR EACH ROW clause, and should loop through the collection data and perform the update for each row.

    See Tom Kyte's example, Case 1.

  3. #3
    Join Date
    Oct 2008
    Posts
    2

    not sure

    Bot sure how to do that exactly. Any syntax help would be great..

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What is your trigger actualy trying to do, in simple words? If all it wants to do is update a column in the very row you are inserting or updating you don't need to perform an update statement at all:
    Code:
    create or replace trigger postaladdress_before
    before insert or update
    OF ADDRESS1 ,ADDRESS2 ,ADDRESS3 ,ADDRESS4 ,ADDRESS5 ,ADDRESS6 ,ADDRESS7
    on tocpostaladdress
    for each row
       :new.postcode := my_package.function(:new.address1, :new.address2, ...);
    end;

Posting Permissions

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