Select In Trigger [message #256836] |
Mon, 06 August 2007 20:56  |
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 #256842 is a reply to message #256836] |
Mon, 06 August 2007 22:15   |
flyboy
Messages: 1903 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 #256850 is a reply to message #256836] |
Mon, 06 August 2007 22:37   |
 |
BlackSwan
Messages: 26766 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 #257163 is a reply to message #256836] |
Tue, 07 August 2007 12:26   |
Bill B
Messages: 1971 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
|
|
|
|