Recently we learnt how to audit a table using DML Triggers in Oracle database now we will see how we can make a synchronized backup copy of a table using the same. By synchronized backup copy I mean the backup table gets automatically populated or updated with the main table simultaneously. [Tweet This]
For the demonstration we will require two identical tables; one which will serve as your main table that will accept the data from your database user and the second which will be your backup table. I will use the Superheroes table which we have been using since the beginning of this DML trigger series as our main table.
Next we will have to create an identical table to this one which will work as our backup table.
Let’s create this backup table.
Suggested Reading: Create Table As Command.
Next we have to write the trigger which will insert, update or delete the rows from the backup table when someone does the same with our main table.
After successful execution of the trigger, changes from the main table will get reflected on the backup table too. For detail explanation please watch the video on the same topic.
Before ending up this blog a quick disclaimer, though you can write this trigger for any table but I would not advise you to use such trigger on those tables that involve heavy data input, deletion and updation. This is chiefly due to the performance reasons.
Hope you enjoyed this tutorial. If so then tweet and tell me @RebellionRider. Also share this blog on you social with your friends or by any means you find convenient. Thanks & have a great day!
You can DOWNLOAD SQL script and presentation used in the Video and in this article.