I need help on how can i write a trigger for updating backup table on DML changes on source table.
I have source table named APP_SOURCE and backup table name APP_BACKUP with same data and structure.
On any DML on source table APP_SOURCE it corresponding backup table APP_BACKUP should also be updated with same number of record changes on any DML
(insert/update/delete) to maintain data consistency on both tables
For cross refernce all changed DML records on source table APP_SOURCE should be tracked and stored within third table APP_SOURCEDMLTRACK storing old value and new value data before and after dml changes on source table APP_SOURCE
Instead of writing triggers, you put a materialized view log on your source table. Then you can turn your backup table into a materialized view that is based on the source table. When you perform a fast refresh of the backup table MV, it will read the MV log on the source table. Refreshes should take almost no time at all to process.
If the data must be absolutely up-to-date at all times, you can use refresh on commit so that the MV is always in synch with the table. Normally I recommend against using refresh on commit, and instead recommend a short refresh interval.
You can use triggers as you suggest, but it seems simpler to use something like MVs to create a shadow table.