Re: Oracle trigger problem (Error: ORA-04091)

On Fri, 01 Jun 2007 13:02:35 -0700, Anthony Smith <mrsmithq_at_hotmail.com> wrote:

>I am trying to create a trigger where when a new row is added to a
>certain table, it updates one of the columns from that table based on
>another sql statement.
>
>Here is my trigger:
>
>create or replace TRIGGER AAR023.KIAC_ACCT_INSERT_TRIGGER
> AFTER INSERT ON AAR023.KIAC_ACCOUNTS
> for each row
> BEGIN
> update aar023.kiac_accounts set corp_id =
> (select corp_id_nbr from aar023.corp_id where comp_nm
>= :new.cust_nm)
> where kiac_acct_nbr = :new.kiac_acct_nbr;
>END;
>
>When I run sql loader to populat the KIAC_ACCOUNTS table I get this
>error:
>
>Record 1: Rejected - Error on table KIAC_ACCOUNTS.
>ORA-04091: table AAR023.KIAC_ACCOUNTS is mutating, trigger/function
>may not see it
>ORA-06512: at "AAR023.KIAC_ACCT_INSERT_TRIGGER", line 5
>ORA-04088: error during execution of trigger
>'AAR023.KIAC_ACCT_INSERT_TRIGGER'
>
>
>I guess the problem is because I am trying to do an update on the
>triggered table, but I am doing this after the insert so this should
>be fine.
>
>How do I get around this error?

I would recommend fix the problem, which is a badly denormalized design.
Apart from that, the update is an apparent attempt to screw up the application even further, because nothing would have happened if you would just have coded in a *before insert for each row* trigger

select corp_id_nbr into :new.corp_id
from aar023.corp_id
where comp_nm = :new.cust_nm

That said, I would seriously recommend you take a step back and read up on application development and database design, or both you and your employer will end up in hell.
Just the idea you prefix every statement with the statement owner makes me shudder.

-- 
Sybrand Bakker
Senior Oracle DBA