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

On Jun 1, 1:02 pm, Anthony Smith <mrsmi..._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?

Goto asktom.oracle.com and put the word mutating in the search box.

jg

--
@home.com is bogus.
http://www.grab.com/boards/b/home/topic/45915