By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,492 Members | 1,289 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,492 IT Pros & Developers. It's quick & easy.

Help with Trigger Updating same row?

P: 4
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
Feb 8 '08 #1
Share this Question
Share on Google+
3 Replies


P: 93
Try This......................
Expand|Select|Wrap|Line Numbers
  1.  
  2. create table b2 (f1 number(3), f2 varchar2(10));
  3. //
  4. insert into b2 values(100,'abcd');
  5. //
  6.  create or replace trigger subtrig
  7.  before/after insert on b2
  8.  begin
  9.  update b2 set f2='wxyz' where f1=100;
  10.  end;
  11.  //
  12.  select * from b2 ;
  13. //
  14. insert into b2 values(200,'efgh');
  15. //
  16. select * from b2;
  17. //
  18.  
  19. note:- to avoid mutation problem use Temporary table solution and another one example to avoid mutation.
  20.  
  21. create or replace trigger  mutating before delete on emp1
  22.  for each row
  23.  begin
  24.  insert into emp1(empno,ename,job,sal)
  25.  values(1000,'raj','it',8000);
  26.  update emp1
  27.  set sal = 9000
  28.  where empno=2100;
  29.  end mutating;
  30.  /
  31.  
  32. SQL> delete from emp1
  33.   2  where empno=7846;
  34. delete from emp1
  35.             *
  36. ERROR at line 1:
  37. ORA-04091: table SCOTT.EMP1 is mutating, trigger/function may not see it
  38. ORA-06512: at "SCOTT.MUTATING", line 2
  39. ORA-04088: error during execution of trigger 'SCOTT.MUTATING'
  40. /
  41.  
  42. solution:-
  43.  
  44.  create or replace trigger  mutating before delete on emp1
  45. -- for each row
  46.  begin
  47.  insert into emp1(empno,ename,job,sal)
  48.  values(1000,'raj','it',8000);
  49.  update emp1
  50.  set sal = 9000
  51.  where empno=2100;
  52.  end mutating;
  53. /
  54. delete from emp1
  55. where empno=1000;
  56. /
  57.  
Feb 9 '08 #2

amitpatel66
Expert 100+
P: 2,367
subashsavji,

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

MODERATOR
Feb 10 '08 #3

P: 4
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?
Feb 11 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.