After Insert trigger

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Working with Oracle » After Insert trigger

After Insert trigger

Rate Topic

Display Mode

Topic Options

Author

Message

rajasekhar.cn

Posted Monday, January 28, 2013 2:54 AM

Forum 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

Jo Pattyn

Posted Monday, January 28, 2013 9:50 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 1,333, Visits: 4,415

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 »

Permissions