Strange Trigger behaviour

730392

I 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 [email protected] 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 [email protected] (
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;

21205

why aren't you using Oracle's built in replication for this?

Anurag Tibrewal

Hi,

This morning's text file was processed, but failed to insert records in either table.

Did you get any error? If yes what is the error.
I do not see any reason why you would not be getting any error, if the data is not inserted?

Yesterday's hourly feed caused 35 records to be inserted in exchange_rate, but only 12 of these were echoed to DM107ExchangeRatesODS.

Are you sure all these 35 records has distinct TS.CNTR_CRNCY_ID, TS.RATE_PRPS AND TS.REF_CURNCY. Why do you believe that out of these 35 records/trigger none trigger would have deleted the records from DM107ExchangeRatesODS

Regards
Anurag

730392

I'm not using Oracle's built-in replication, because it's not appropriate for the task.

You are right about the 12/35 issue, further data analysis shows that the hourly feed records are not unique on the three columns used for the deletion, so only the most recent row for those three values is retained. This is the intended behaviour, but I hadn't thought that through.

So the only issue that remains is why nothing from the daily feed was processed. I haven't seen any errors, it just fails silently.

21205

user2143457 wrote:
I'm not using Oracle's built-in replication, because it's not appropriate for the task.

Not appropriate?... Could you elaborate on that?

Anurag Tibrewal

user2143457 wrote:

So the only issue that remains is why nothing from the daily feed was processed. I haven't seen any errors, it just fails silently.

This is something hard to believe.
As far as I know Oracle does not have any failure without any error reporting and its typically ORA-XXXX.
It is possible you are suppressing this error in your code. If that is the case check your code and then check for the error.

If it is not possible to change the code. Can you run a small test with the below code as your trigger

CREATE OR REPLACE TRIGGER EXRATE_AFT_INS_ROW_ODS
AFTER INSERT ON EXCHANGE_RATE
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
DELETE FROM [email protected] 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 [email protected] (
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
);

EXCEPTION
  WHEN OTHERS THEN  
     insert into error_tab(SQLCODE,SQLERRM);

END;

Here it is assumed you have a table named error_tab which has two columns that can store the above value.

Regards
Anurag Tibrewal.

kendenny

Is PRC_DT the effective date? If so are you loading the records in PRC_DT order? If not you could load the most current rate and then overlay it with an older rate.

730392

The reason it's not applicable is that I'm not simply replicating, I am removing old records and only retaining the most recent record.

730392

Yeah, unfortunately the code which processes the morning file and does the inserts is a third-party app to which I don't have source access, and it is emminently possible that they are suppressing the error.

I'll give the code you provided a go, as soon as I finish sorting the Live system issues this has created, and can get on to a test system.

Thanks.

730392

prc-dt is indeed the efefctive date, but is supplied by the original data-provider in the data field, and is the date of the last working day. It has no time field.

Not sure what you mean by "load the most current rate and then overlay it with an older rate", there isn't always a current rate on the Bridge system, and the basis for retention is that we should keep in the Brige system only the most recent record added to the Live system, for a given Currency, Counter-currency and purpose.

21205

I think it's a great candidate for replication... after loading the file, refresh...
something like

SQL> create table test
  2  as
  3  select rownum id
  4       , 'THIS' cd
  5       , sysdate - rownum dt
  6    from dual
  7   connect by level <= 5
  8  /

Table created.

SQL> 
SQL> alter table test
  2  add constraint test_pk primary key (id)
  3  /

Table altered.

SQL> 
SQL> create materialized view log
  2  on test
  3  /

Materialized view log created.

SQL> 
SQL> conn usr/[email protected]_side
Connected.
SQL> 
SQL> create materialized view test_mv
  2  as
  3  select cd
  4       , max (dt)
  5    from [email protected]_link
  6   group by cd
  7  /

Materialized view created.