Reading changes done in table and replicate them to another system

I have to insert data into Oracle digital assistant(ODA) by calling an API POST method service. I am reading data from an Oracle table where all the transactions happened in a day, and my system needs to be inserted into ODA(Consider it as another database).

I have created a trigger on the main table which will insert all the transaction which happens in a day to another table. Using jdbc I will read this table and write the data into file. Then I will call the API which will inject the data into ODA. Once data will be inserted into ODA I will truncate the table so that next day it inserts fresh data.

I am writing trigger on main table so any DML operation happened on the table needs to be inserted into another table. I was also not aware that I had to reflect the changes done by updates or deletes.

After reading all changes I will truncate the replicated table where the changes are located.

Is there any better approach that a trigger DML ?

I won't use a trigger in this case. What happens if you have deletes or updates in the main table and you use only a trigger for inserts ? In this case, I would use a MATERIALIZED VIEW LOG to get the changes

let me show you how

SQL> create table t ( c1 number primary key, c2 number ) ;

Table created.

SQL>  declare
 begin
 for i in 1 .. 100000
 loop
 insert into t values ( i , dbms_random.value ) ;
 end loop;
 commit ;
 end;
 /  2    3    4    5    6    7    8    9

PL/SQL procedure successfully completed.

SQL> create materialized view log on t with primary key ;

Materialized view log created.

SQL> select count(*) from t ;

  COUNT(*)
----------
    100000

Now we have a materialized view log which will replicate all changes done in the source table using the primary key

SQL> insert into t values ( 100001 , dbms_random.value ) ;

1 row created.

SQL>  select count(*) from t ;

  COUNT(*)
----------
    100001

SQL> select count(*) from mlog$_t ;

  COUNT(*)
----------
         1

Now, during the night you want to reflect only the changes done in the target materialized view

1.First lock the original table to avoid any operation

SQL> LOCK TABLE MY_TEST ;

2.Changes are in the log mview

SQL> select * from t where c1 in ( select c1 from mlog$_t ) ;

       C1         C2
---------- ----------
    10000! .276556329

3.Once you got all changes copied to the API , then purge the log

SQL> BEGIN
DBMS_SNAPSHOT.PURGE_LOG (
master => 'T',
num => 1,
flag => 'DELETE');
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL>  select count(*) from mlog$_t ;

  COUNT(*)
----------
         0

With this you will be sure that all changes are covered.

In your case, I think you don't even need the materialized view, only the materialized view log