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

[raf]

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

[Turin]

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.

[raf]

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