Home » SQL & PL/SQL » SQL & PL/SQL » avoid muitating table error
avoid muitating table error [message #337403] Wed, 30 July 2008 16:04 Go to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Windows Professional 2002 and Oracle Release 9.2.0.8.0

Hi

 1  CREATE or replace trigger test_table2
  2    after insert on test
  3    REFERENCING NEW AS NEW OLD AS OLD
  4    for each row
  5  declare
  6    v_cnt varchar2(25);
  7  begin
  8  if inserting then
  9  :new.col1 := 123;
 10  end if;
 11* end;
SQL> 
CREATE or replace trigger test_table2
                                  *
ERROR at line 1:
ORA-04084: cannot change NEW values for this trigger type


As per of situation, i need to create this trigger only after the insert of any row on the table. Meaning, once the row get inserted then i need to this trigger.

The reason i am avoiding to create trigger before the insert operation, there is already 2 trigger getting fire before the insert operation and this trigger is dependable on those 2 triggers and only need to execute after those triggers gets fire.

I am not looking for detail answer, any small hint would be much much appreaciated.

[Updated on: Wed, 30 July 2008 16:05]

Report message to a moderator

Re: avoid muitating table error [message #337404 is a reply to message #337403] Wed, 30 July 2008 16:11 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>any small hint would be much much appreaciated.
Don't ask Oracle to take any action it is not allowed to take.
Within a trigger, the code can is prevented from doing DML or SELECT against the table on which the trigger is created.
Re: avoid muitating table error [message #337405 is a reply to message #337404] Wed, 30 July 2008 16:21 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Sorry, sometime i am too dull to understand anything.. Could you please further expand your point..

If you meant calling procedure in the trigger. I have tried that way too, but same results.

Apprecaite your valuable time on my question..

[Updated on: Wed, 30 July 2008 16:25]

Report message to a moderator

Re: avoid muitating table error [message #337406 is a reply to message #337403] Wed, 30 July 2008 16:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
unwilling or incapable to RTFM?

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1287

[Updated on: Wed, 30 July 2008 16:28] by Moderator

Report message to a moderator

Re: avoid muitating table error [message #337436 is a reply to message #337403] Wed, 30 July 2008 21:45 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
this is not an issue of mutating tables. That is a different error.

consider the following question?

Quote:
What is the difference between the before insert trigger and the after insert trigger?

What does it mean to do :new.col1 = 123; in a before insert trigger?

What does is mean to do :new.col1 = 123; in an after insert trigger?

Where is the row you are inserting BEFORE INSERT?
Where is the row you are inserting AFTER INSERT?

What does :new.col1 represent?


Good luck, Kevin
Re: avoid muitating table error [message #337472 is a reply to message #337403] Thu, 31 July 2008 00:54 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Logically thinking, if you are AFTER insert then value is already inserted and so you cannot modify it.

If you have dependent triggers then there are logically NOT several triggers but ONE trigger that you split, merge them.

In addition, I don't the connection between the title and the error.

Regards
Michel

[Updated on: Thu, 31 July 2008 00:56]

Report message to a moderator

Previous Topic: Fetching Recorset from a Stored Proc
Next Topic: SQL Error
Goto Forum:
  


Current Time: Thu Dec 08 08:40:16 CST 2016

Total time taken to generate the page: 0.08014 seconds