I have this scenario where we need to capture changes (updates/deletes) to transaction tables in our OLTP database. The changes will be populated to temp tables and then moved to Hadoop for analysis. The OLTP table record huge volume of data on a daily basis, some tables having thousands of updates every day and some having a few million.
We have thought of using a trigger-based approach, where a trigger in the OLTP tables will take care of inserting records in the temp tables. It will be a "After Update and Delete" trigger as:
IF updating THEN
BEGIN
insert into temp table // insert statement here
EXCEPTION
WHEN others THEN
null;
END;
ELSIF DELETING THEN
BEGIN
insert into temp table //insert statement here
EXCEPTION
WHEN others THEN
null;
END;
END IF;
END;
Please let me know if this is an acceptable approach. Are there any better ways of doing the same?
I am using Oracle 11g r1.