Help with Trigger Updating same row?

[chrisindfw]

Hello all!

I have seen very wide discussions on this before on the web but I was wondering what your thoughts were.

I have a table where when a record is added, I want to update that row. Sounds simple. But I am getting the nasty error about mutating tables.

Here is a very generic summary.

TABLE_A
Field1 Number
Field2 Text

Insert into Table_A (Field1) Values (1000);

I have a trigger that I want to do the following.

Update Table_A Set Field2 = 'Changed Record' where Field1 = 1000;


That is a very rough design to spare the technical details but I am wondering what I need to do in order to update the same record that caused the trigger. I only want the trigger to fire on insert and not update/delete.

Thanks in advance for your time.
Chris

[subashsavji]

Try This......................

 
create table b2 (f1 number(3), f2 varchar2(10));
//
insert into b2 values(100,'abcd');
//
 create or replace trigger subtrig
 before/after insert on b2
 begin
 update b2 set f2='wxyz' where f1=100;
 end;
 //
 select * from b2 ;
//
insert into b2 values(200,'efgh');
//
select * from b2;
//
 
note:- to avoid mutation problem use Temporary table solution and another one example to avoid mutation.
 
create or replace trigger  mutating before delete on emp1
 for each row
 begin
 insert into emp1(empno,ename,job,sal)
 values(1000,'raj','it',8000);
 update emp1
 set sal = 9000
 where empno=2100;
 end mutating;
 /
 
SQL> delete from emp1
  2  where empno=7846;
delete from emp1
            *
ERROR at line 1:
ORA-04091: table SCOTT.EMP1 is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.MUTATING", line 2
ORA-04088: error during execution of trigger 'SCOTT.MUTATING'
/
 
solution:-
 
 create or replace trigger  mutating before delete on emp1
-- for each row
 begin
 insert into emp1(empno,ename,job,sal)
 values(1000,'raj','it',8000);
 update emp1
 set sal = 9000
 where empno=2100;
 end mutating;
/
delete from emp1
where empno=1000;
/
 

[amitpatel66]

subashsavji,

Can you make sure you will use [code] tags in future for any source code you post in this forum?

MODERATOR

[chrisindfw]

Let me see if I have this straight.

- Put a trigger on my main table that inserts into a temporary table.

- Put a trigger on the temp table that does the update on the main table?

Do I have that right?