DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: ORA-04091: table AM is mutating, trigger/function may not see it

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    ORA-04091: table AM is mutating, trigger/function may not see it

    I create this trigger:

    CREATE OR REPLACE TRIGGER pr_test
    after insert ON AM
    FOR EACH ROW
    declare
    appo_pr varchar2(64):= null;
    BEGIN
    select name
    into appo_pr
    from AS
    where AS_ID=:new.AS_ID;
    insert into RM (name) values (appo_pr);
    END pr_test;

    I insert before a new AS_ID and name in tab AS but when I insert a new record in tab AM I get this error:
    ORA-04091: table AM is mutating, trigger/function may not see it
    ORA-06512: at PR_TEST", line 11
    ORA-04088: error during execution of trigger PR_TEST'

    I'd like to create a trigger that when I insert a new record in AM It verify the value in col name of AS and insert this value in col name of AM.

    What I wrong in this trigger??
    How can I modify it??

    Thanks Raf

  2. #2
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93
    Ok, I think your trigger is created for the INSERT event of the table AM, but you're trying to make a SELECT from the same AM table in the body of this trigger. As you know, a TRIGGER has the posibility to change the information of a table where it is created for and you're trying to read data from the same table.

    Just let know to Oracle the values of the table AM before you have the possibility to change the data by using AUTONOMOUS_TRANSACTION pragma.

    According with Oracle documentation,

    "The AUTONOMOUS_TRANSACTION pragma instructs the PL/SQL compiler to mark a routine as autonomous (independent). An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction."


    So, you can use the PL/SQL pragma autonomous_transaction clausule like:

    CREATE OR REPLACE TRIGGER pr_test
    after insert ON AM
    FOR EACH ROW
    declare
    pragma autonomous_transaction;
    appo_pr varchar2(64):= null;
    BEGIN
    select name
    into appo_pr
    from AS
    where AS_ID=:new.AS_ID;
    insert into RM (name) values (appo_pr);
    END pr_test;


    Good Look!
    Regards.

  3. #3
    Join Date
    Jul 2002
    Posts
    228
    I create this trigger:

    CREATE OR REPLACE TRIGGER pr_test
    BEFORE insert ON AM
    FOR EACH ROW
    BEGIN
    select name
    into :new.name
    from AS
    where AS_ID=:new.AS_ID;
    END pr_test;

    It run correctly if I insert a new value with a new AS_ID

    col AS_ID is not a primary key but is a foreign key on AS
    in AM primary key is AM_ID
    when I insert a new record in AM without AS_ID I get:
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at "PR_TEST", line 2
    ORA-04088: error during execution of trigger 'PR_TEST'

    I'd like to create a trigger that when I insert a new record in AM (also without AS_ID) It verify the value in col NAME of AS and insert this value in col name of AM.
    i.e. if AS_id is not set (null) I get AS_id from table AS and I get name from AS automatically

    How can I modify it??

    Thanks Raf

Posting Permissions

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


Click Here to Expand Forum to Full Width