Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Trigger based approach to capture changes in OLTP DB

Rohit.Kapoor01May 23 2013 — edited Jun 6 2013
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.
This post has been answered by unknown-7404 on May 23 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 4 2013
Added on May 23 2013
12 comments
2,179 views