Oracle trigger problem (Error: ORA-04091)

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? Received on Fri Jun 01 2007 - 15:02:35 CDT