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.

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 AS 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
BEFORE INSERT OR DELETE OR UPDATE ON superheroes
FOR EACH ROW
ENABLE 
BEGIN
  IF INSERTING THEN
    INSERT INTO superheroes_backup (SH_NAME) VALUES (:NEW.SH_NAME);  
  ELSIF DELETING THEN
    DELETE FROM superheroes_backup WHERE SH_NAME =:old.sh_name; 
  ELSIF UPDATING THEN
    UPDATE superheroes_backup 
    SET SH_NAME =:new.sh_name WHERE SH_NAME =:old.sh_name;
  END IF;
END;
/

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!