Home » SQL & PL/SQL » SQL & PL/SQL » Select In Trigger
Select In Trigger [message #256836] Mon, 06 August 2007 20:56 Go to next message
blueplate
Messages: 3
Registered: August 2007
Junior Member

CREATE OR REPLACE TRIGGER TRIGGER_INSERT_IATA_AIRPORT
AFTER INSERT
ON XODB4.FMM_IATA_AIRPORT
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
INSERT INTO FMM_IATA_AIRPORT@TESFIDS
(select * from XODB4.FMM_IATA_AIRPORT Where IATA_AIRPORT_CODE = :new.IATA_AIRPORT_CODE);
exception
when others then
null;
END TRIGGER_INSERT_IATA_AIRPORT;


I was wondering why is my trigger not working? is there something wrong? Please advise...

Thanks
Re: Select In Trigger [message #256839 is a reply to message #256836] Mon, 06 August 2007 21:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I was wondering why is my trigger not working?
My car is NOT working.
>is there something wrong? Please advise...
Tell me how to fix my car.

No Operating System name or version.
No Oracle version to 4 decimal places.
No code formatting (see http://www.orafaq.com/forum/t/59964/74940/)
No error message.
No help. [You're On Your Own (YOYO)!]
Re: Select In Trigger [message #256842 is a reply to message #256836] Mon, 06 August 2007 22:15 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
CREATE OR REPLACE TRIGGER TRIGGER_INSERT_IATA_AIRPORT
<specification>
BEGIN
<insert statement>
exception
when others then
null;
END TRIGGER_INSERT_IATA_AIRPORT;

The trigger is working as (poorly) coded.
If any exception occurs, it silently ignores it.
What about removing the exception block and running it again?
Re: Select In Trigger [message #256845 is a reply to message #256842] Mon, 06 August 2007 22:26 Go to previous messageGo to next message
blueplate
Messages: 3
Registered: August 2007
Junior Member

I'm sorry If its not correctly formatted:

I made a more simple trigger, it returns the same error :

CREATE OR REPLACE TRIGGER BIMA.FIDS
AFTER INSERT ON BIMA.TESFIDS
REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

BEGIN

DELETE FROM BIMA.TESFIDS WHERE NUM = 9;

END FIDS;


And the error message was this:

ORA-04091: table BIMA.TESFIDS is mutating, trigger/function may not see it
ORA-06512: at "BIMA.FIDS", line 4
ORA-04088: error during execution of trigger 'BIMA.FIDS'
Re: Select In Trigger [message #256846 is a reply to message #256836] Mon, 06 August 2007 22:28 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I made a more simple trigger, it returns the same error :
>ORA-04091: table BIMA.TESFIDS is mutating, trigger/function may not see it
It appears you are unwilling or incapable for searching this forum or using GOOGLE.

Results 1 - 10 of about 568 for Oracle ORA-04091
Re: Select In Trigger [message #256849 is a reply to message #256846] Mon, 06 August 2007 22:33 Go to previous messageGo to next message
blueplate
Messages: 3
Registered: August 2007
Junior Member

anacedent wrote on Mon, 06 August 2007 22:28
>I made a more simple trigger, it returns the same error :
>ORA-04091: table BIMA.TESFIDS is mutating, trigger/function may not see it
It appears you are unwilling or incapable for searching this forum or using GOOGLE.

Results 1 - 10 of about 568 for Oracle ORA-04091



If you don't want to help, just don't help... no need to make remarks.
Re: Select In Trigger [message #256850 is a reply to message #256836] Mon, 06 August 2007 22:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You should read & FOLLOW the posting guidelines as stated in the STICKY post at top of forum.
http://www.orafaq.com/forum/t/42428/74940/
Especially #4 & #5
#4 Did I search the board properly? Have I also tried the Site Search Engine? A large group of the questions posted here are just a déjà -répondu of the past.

#5Did I use Google? Even if your question hasn't been answered here, chances are that on other websites someone already has posted an answer.


If you don't like the timeliness, correctness or tone of any response; you are entitled to a full refund.

[Updated on: Mon, 06 August 2007 22:38] by Moderator

Report message to a moderator

Re: Select In Trigger [message #256865 is a reply to message #256836] Mon, 06 August 2007 23:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition to Ana's posts:
ora-04091: 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.

Just have a look at trigger sections in "Application Developer's Guide - Fundamentals" book.

Regards
Michel
Re: Select In Trigger [message #256900 is a reply to message #256865] Tue, 07 August 2007 01:18 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Perhaps this little post might help. Basically it comes down to this: you're performing DML (INSERT, SELECT,...) on the same table that has triggered the event. At that time, you're already modifying the table. It's content is changing, it is mutating. This DML can again fire some triggers, perhaps even the same trigger. Read the link. Search the board. Most of the time this error can be avoided. It often indicates a design flaw.

MHE
Re: Select In Trigger [message #257163 is a reply to message #256836] Tue, 07 August 2007 12:26 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Rewrite the trigger to not use a select. If you want to insert into a table on another database when the first one is updated, then simply use the new columns.


CREATE OR REPLACE TRIGGER TRIGGER_INSERT_IATA_AIRPORT
AFTER INSERT
ON XODB4.FMM_IATA_AIRPORT
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
INSERT INTO FMM_IATA_AIRPORT@TESFIDS
values(:new.col1,:new.col2,:new.col3....);
exception
when others then
null;
END TRIGGER_INSERT_IATA_AIRPORT;

[Updated on: Tue, 07 August 2007 12:26]

Report message to a moderator

Re: Select In Trigger [message #257165 is a reply to message #257163] Tue, 07 August 2007 12:30 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Even if it is in the original post don't propagate the awful:
when others then null;

Please don't.

Regards
Michel
Previous Topic: Decimal to Hex conversion of function?
Next Topic: special characters in between the string
Goto Forum:
  


Current Time: Sun Dec 11 08:21:06 CST 2016

Total time taken to generate the page: 0.11359 seconds