Strange Trigger behaviour
730392Oct 27 2009 — edited Oct 27 2009I am seeing very strange behaviour from a trigger. I'll start with a little background. I have two databases as follows:
Oracle 9.2.0.8 on Solaris - which I will refer to as Live.
Oracle 9.2.0.1 on Windows - which I will refer to as Bridge.
In Live, we have a table exchange_rate, which contains historical records for exchange rates.
In Bridge, we have a table DM107ExchangeRatesODS, which contains only the most recent exchange rates.
Records are added to exchange_rate in two ways. Each morning a text file is received containing exchange rate data for the previous business day. This causes about 500 records to be inserted as it is processed. Hourly, throughout the day a secondary feed is checked, which can result each time in a few new records.
We recently added a trigger to exchange_rate (which I will list below) to maintain DM107ExchangeRatesODS. This has resulted in the following behaviour.
This morning's text file was processed, but failed to insert records in either table.
Yesterday's hourly feed caused 35 records to be inserted in exchange_rate, but only 12 of these were echoed to DM107ExchangeRatesODS.
I can't see anything wrong with the trigger, but removing it allowed all records in this morning's text file to be successfully processed and inserted into exchange_rate.
Any thoughts?
The trigger is: (DM53 is the database link from Live to Bridge, both deletions and insertions work fine over this link when entered directly into SQLPLUS)
CREATE OR REPLACE TRIGGER EXRATE_AFT_INS_ROW_ODS
AFTER INSERT ON EXCHANGE_RATE
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
DELETE FROM DM107EXCHANGERATESODS@DM53 TS
WHERE TS.CNTR_CRNCY_ID = :NEW.CNTR_CRNCY_ID
AND TS.RATE_PRPS = :NEW.RATE_PRPS
AND TS.REF_CURNCY = :NEW.REF_CURNCY;
INSERT INTO DM107EXCHANGERATESODS@DM53 (
CNTR_CRNCY_ID, RATE_PRPS, PRC_DT, REF_CURNCY,
AMT_FROM, BID_RATE, AMT_TO
) VALUES (
:NEW.CNTR_CRNCY_ID, :NEW.RATE_PRPS, :NEW.PRC_DT, :NEW.REF_CURNCY,
:NEW.AMT_FROM, :NEW.BID_RATE, :NEW.AMT_TO
);
END;