Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: 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 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

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

    I think this does what you intended:

    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;

    But this is denormalising the as.name into pr_test! Tsk, tsk...

  3. #3
    Join Date
    Jul 2002
    Posts
    227

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

    Originally posted by andrewst
    I think this does what you intended:

    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;

    But this is denormalising the as.name into pr_test! Tsk, tsk...
    ok
    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.

    How can I modify it??

    Thanks Raf

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

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

    Do you want this?

    Code:
    CREATE OR REPLACE TRIGGER pr_test
    BEFORE insert ON AM
    FOR EACH ROW
    BEGIN
      IF :new.AS_ID IS NOT NULL THEN
        select name
        into :new.name
        from AS
        where AS_ID=:new.AS_ID;
      END IF;
    END pr_test;
    i.e. only look up NAME if AS_ID has been set?

  5. #5
    Join Date
    Jul 2002
    Posts
    227
    YES,
    but I want also that if AS_id is not set (null) I get AS_id from table AS and I get name from AS automatically.

    Thanks
    Raf

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    But presumably table AS contains many records, so which one would you get the AS_ID and name from?

  7. #7
    Join Date
    Jul 2002
    Posts
    227
    AS contains AS_ID primary key and name
    I'd like to get in name of AM the same name of AS

    ie. tab AS
    as_id.............name
    001...............TOM

    Tab AM
    am_id........as_id.........name
    01.............001...........TOM


    Raf

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Are you saying table AS contains only one record???

    If not, suppose AS contains:

    as_id, name
    001, TOM
    002, FRED
    003, MARY

    Then when you insert:

    insert into AM (am_id, as_id, name) values (100, null, null);

    which AS_ID and NAME from AS should the trigger pick?

    I'm clearly missing something here!

  9. #9
    Join Date
    Jul 2002
    Posts
    227
    value 100 must be in AS mandatory!!!!!



    Raf

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Why?

  11. #11
    Join Date
    Jul 2002
    Posts
    227
    because AS_id is a primary key on AS
    AS_ID is foreign key on AM

  12. #12
    Join Date
    Jul 2002
    Posts
    227
    and I want only the new records with AS_ID of AS mandatory!!

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Let's step back a bit here, it is getting extremely confusing. You said earlier:

    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 took that to mean you were inserting an AM record without specifying an AS_ID value, like this:

    insert into AM (am_id, as_id, name) values (100, null, null);

    (Note: 100 here is the AM_ID, not the AS_ID!)

    Do you want to be able to do that? And if so, what do you want the trigger to do?

  14. #14
    Join Date
    Jul 2002
    Posts
    227
    I want to be able to have AS_ID and NAME in AM.

    I want the trigger insert automatically AS_IS and NAME in AM.

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sorry, but I give up! You didn't answer my question. If you don't specify an AS_ID when you insert into AM, how is the trigger supposed to choose an AS record to get the AS_ID and NAME from? Or is AS a 1-row table?

    Perhaps if you could give an example with realistic data, showing what you want to happen?

Posting Permissions

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