Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

After Insert trigger Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 2:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 28, 2013 4:35 AM
Points: 3, Visits: 6
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.
Post #1412285
Posted Monday, January 28, 2013 9:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:34 PM
Points: 1,356, Visits: 5,676
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?
Post #1412518
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse