Synchronized Table Backup in PL/SQL

By Manish Sharma

Synchronized Table Backup Using DML Trigger

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.

 CREATE TABLE  superheroes(
  Sh_name  VARCHAR2(30)

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.

 CREATE TABLE superheroes_backup
 SELECT * FROM superheroes WHERE 1=2;
The above command will create the identical table just like the main table superheroes only without data.

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.

 CREATE or REPLACE trigger Sh_Backup
   INSERT INTO superheroes_backup (SH_NAME) VALUES (:NEW.SH_NAME);
   DELETE FROM superheroes_backup WHERE SH_NAME =:old.sh_name;
   UPDATE superheroes_backup
   SET SH_NAME =:new.sh_name WHERE SH_NAME =:old.sh_name;

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!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

NO SQL Script used in Video and in this article

Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn