Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Unanswered: table mutating error

    Hi,

    I have a table et_trial which has column trial_id, date_last_updated etc.
    trial_id is the primary key and date_last_updated is a date field. I need to update date_last_updated whenever there is any update on this table. I wrote two types of triggers, but I still get table mutating error, please help me to trouble shoot this problem. The first type is:

    CREATE OR REPLACE package et_trial_new_pkg
    as
    type array is table of et_trial%rowtype index by binary_integer;
    newvals array;
    empty array;
    end;
    /

    CREATE OR REPLACE package et_trial_old_pkg
    as
    type array is table of et_trial%rowtype index by binary_integer;
    oldvals array;
    empty array;
    end;
    /

    CREATE OR REPLACE TRIGGER et_trial_new_bu
    BEFORE UPDATE of private_name ON et_trial
    begin
    et_trial_new_pkg.newvals := et_trial_new_pkg.empty;
    end;

    CREATE OR REPLACE TRIGGER et_trial_new_bufer
    BEFORE UPDATE of private_name ON et_trial
    for each row
    declare
    i number default et_trial_new_pkg.newvals.count+1;
    begin
    et_trial_new_pkg.newvals(i).private_name := :new.private_name ;
    end;

    CREATE OR REPLACE TRIGGER et_trial_old_bu
    BEFORE UPDATE of private_name ON et_trial
    begin
    et_trial_old_pkg.oldvals := et_trial_old_pkg.empty;
    end;

    CREATE OR REPLACE TRIGGER et_trial_old_bufer
    BEFORE UPDATE of private_name ON et_trial
    for each row
    declare
    i number default et_trial_old_pkg.oldvals.count+1;
    begin
    et_trial_old_pkg.oldvals(i).private_name := ld.private_name;
    end;

    CREATE OR REPLACE TRIGGER trial_date_last_updated_t
    AFTER UPDATE of private_name ON et_trial
    FOR EACH ROW
    begin
    for i in 1 .. et_trial_new_pkg.newvals.count loop
    IF et_trial_old_pkg.oldvals(i).private_name <> et_trial_new_pkg.newvals(i).private_name THEN
    update et_trial set DATE_LAST_UPDATED = SYSDATE
    where trial_id = et_trial_old_pkg.oldvals(i).trial_id;
    END IF;
    end loop;
    end;

    Then I wrote another type of triggers to solve the problem, but it's still not fixed:

    create or replace package et_trial_pkg
    as
    type ridArray is table of rowid index by binary_integer;

    newRows ridArray;
    empty ridArray;
    g_trigger_updating BOOLEAN := FALSE;
    end;
    /


    create or replace trigger et_trial_bu
    before update on et_trial
    begin
    if(g_trigger_updating = TRUE) then
    et_trial_pkg.newRows := et_trial_pkg.empty;
    end if;
    end;
    /


    create or replace trigger et_trial_aufer
    after update on et_trial for each row
    begin
    if(g_trigger_updating = TRUE) then
    et_trial_pkg.newRows( et_trial_pkg.newRows.count+1 ) := :new.rowid;
    end if;
    end;
    /


    create or replace trigger et_trial_au
    after update on et_trial
    begin
    et_trial_pkg.g_trigger_updating := TRUE;
    for i in 1 .. et_trial_pkg.newRows.count loop
    Update ET_Trial
    set DATE_LAST_UPDATED = SYSDATE
    where rowid = et_trial_pkg.newRows(i);
    end loop;
    et_trial_pkg.g_trigger_updating := FALSE;
    end;
    /

    Any suggestions are welcome.

  2. #2
    Join Date
    Sep 2003
    Location
    Baltimore, MD
    Posts
    8

    try this

    im a new developer who had this problem too. here is the code that worked. it is based on p 301 of scott urman's advanced pl/sql book

    CREATE OR REPLACE PACKAGE SITEDATA AS

    TYPE t_site_id IS TABLE OF site.site_id%TYPE
    INDEX BY BINARY_INTEGER;
    TYPE t_status IS TABLE OF site.status%TYPE
    INDEX BY BINARY_INTEGER;
    TYPE t_site_type IS TABLE OF site.site_type%TYPE
    INDEX BY BINARY_INTEGER;




    v_site_id t_site_id;
    v_status t_status;
    v_site_type t_site_type;
    v_numentries BINARY_INTEGER := 0;

    END SITEDATA;
    /


    CREATE OR REPLACE TRIGGER BSITE_ID
    BEFORE INSERT OR UPDATE ON site
    FOR EACH ROW
    BEGIN

    SITEDATA.v_numentries := SITEDATA.v_numentries + 1;
    SITEDATA.v_site_id(SITEDATA.v_numentries) := :new.site_id;
    SITEDATA.v_status(SITEDATA.v_numentries) := :new.status;
    SITEDATA.v_site_type(SITEDATA.v_numentries) := :new.site_type;



    END BSITE_ID;


    CREATE OR REPLACE TRIGGER PREV_ACCT_REQUEST -- email 01 'Preview Account Request'
    AFTER INSERT ON SITE
    -- Tested on September 29th, 2003, 10:16 am, works fine.
    DECLARE
    v_address1 coordinator.address1%TYPE;
    v_address2 coordinator.address2%TYPE;
    v_city coordinator.city%TYPE;
    v_country country.c_country_dscr%TYPE;
    v_email coordinator.email%TYPE;
    v_fax coordinator.fax%TYPE;
    v_first_name coordinator.first_name%TYPE;
    v_last_name coordinator.last_name%TYPE;
    v_phone coordinator.phone%TYPE;
    v_phone_ext coordinator.phone_ext%TYPE;
    v_postal coordinator.postal%TYPE;
    v_site_name site.site_name%TYPE;
    v_site_id site.site_id%TYPE;
    v_site_type site.site_type%TYPE;
    v_state coordinator.state%TYPE;
    v_status site.status%TYPE;
    v_title coordinator.title%TYPE;
    v_zip coordinator.zip%TYPE;
    f_address2 varchar(75); -- all variables preceded with a f are for formatting
    f_comma varchar(5) := ', ';
    f_ext varchar(1) := 'x';
    f_title varchar2(15) := 'Job title:';
    f_crlf char(2) := chr(13)||chr(10); -- line breaks for the message body

    BEGIN

    --avoid mutating tables, page 301 of Advanced PL/SQL book, author Joel Urman
    FOR v_LoopIndex IN 1..SITEDATA.v_numentries LOOP
    v_site_id := SITEDATA.v_site_id(v_LoopIndex);
    v_site_type := SITEDATA.v_site_type(v_LoopIndex);
    v_status := SITEDATA.v_status(v_LOOPIndex);


    IF v_status = 'PP' AND v_site_type = 1
    THEN
    SELECT c.first_name, c.last_name, c.title, s.site_name, c.address1, c.address2,
    c.city, c.state, NVL(c.phone, 'N/A'), c.phone_ext, NVL(c.fax,'N/A'),
    c.email, c.postal, c.zip, co.c_country_dscr
    INTO v_first_name, v_last_name, v_title, v_site_name, v_address1, v_address2,
    v_city, v_state, v_phone, v_phone_ext, v_fax, v_email, v_postal, v_zip, v_country
    FROM site s, coordinator c, country co
    WHERE s.site_id = v_site_id
    AND c.site_id = v_site_id
    AND c.country = co.c_country_cd;
    ........... --- i took out a large chunk of code not related to the question presented in the forum





    END LOOP;

    SITEDATA.v_numentries := 0;
    END;

  3. #3
    Join Date
    Nov 2003
    Posts
    1

    Lightbulb Mutating Error - Solution, Maybe!

    Hi,

    I saw your code for the trigger "et_trial_au" on the table "et_trial".
    -----------------------------------------------------------------------------------
    create or replace trigger et_trial_au
    after update on et_trial
    begin
    et_trial_pkg.g_trigger_updating := TRUE;
    for i in 1 .. et_trial_pkg.newRows.count loop
    Update ET_Trial
    set DATE_LAST_UPDATED = SYSDATE
    where rowid = et_trial_pkg.newRows(i);
    end loop;
    et_trial_pkg.g_trigger_updating := FALSE;
    end;
    -----------------------------------------------------------------------------------

    If I am not wrong, what you want to do is, you want to update the DATE_LAST_UPDATE column to the current date and time. Right? And hence you are using the UPDATE command.

    OK, Lets understand whats happening here.
    First things first, Oracle says you can not refer to the table, from a trigger or a courser or a package, on which you have written the trigger.
    That means if you are wiritting a trigger "X" on the table "A" then you can not reffer to this table "A" in any way from your trigger "X".

    Second, Your trigger is a "after update" one, so it does not make much sense to write a update inside a update trigger. IF IT WAS ALLOWED then this trigger will keep updating itself for ever and ever till your hardware would give up and crash! JUST JOKING!
    Imagin this, you update a column of your table in the "after update" trigger. Once the update from inside the trigger gets fired the "After Update" trigger again gets fired because you issued a update command and so loooooop!

    Suggession: Why don't you try to just set the value of the column to something like this
    :new.DATE_LAST_UPDATE = sysdate;

    You will just have to check what the new column will become ":new" or ":.old", Its been a long time I wrote a trigger so I forgot it! Hope this will help. All the best!

Posting Permissions

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