After Insert trigger

• rajasekhar.cn

  SSC Rookie

  Points: 29

  More actions

  January 28, 2013 at 2:54 am

  #273510

  Hi ,

  I have a requirement where i have a table (EMP) with fields id, name, status

  What i want is after each insert write the data to another table(EMP_BACKUP) and update the status of EMP table to 1.

  I have written a trigger

  ------------------------------------------------------------------

  create or replace

  trigger fetched_records_trigger

  after insert on EMP for each row

  DECLARE

  --v_username varchar2(10);

  BEGIN

  insert into EMP_BACKUP values (:new.id , :new.COUNT_PER_FETCH, :new.SUCCESS_COUNT, :new.FAILURE_COUNT, :new.FAILURE_DES, :new.STATUS);

  update EMP set status=1;

  END;

  Am able to insert the data into EMP_BACKUP table but cannot update the status column in EMP table to 1

  Am getting the below errror

  -------------------------------

  SQL Error: ORA-04091: table KCBUSER.FETCHED_RECORDS is mutating, trigger/function may not see it

  ORA-06512: at "KCBUSER.FETCHED_RECORDS_TRIGGER", line 6

  ORA-04088: error during execution of trigger 'KCBUSER.FETCHED_RECORDS_TRIGGER'

  04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"

  *Cause: A trigger (or a user defined plsql function that is referenced in

  this statement) attempted to look at (or modify) a table that was

  in the middle of being modified by the statement which fired it.

  *Action: Rewrite the trigger (or function) so it does not read that table.

• Jo Pattyn

  SSC-Dedicated

  Points: 32347

  More actions

  January 28, 2013 at 9:50 am

  #1581699

  You cannot update emp again from an after trigger (could cause looping). Try a before insert trigger.

  Ps why are you updating the whole table (status=1) for each row?