How to create a backup using triggers

In this new article we are going to see how to trace changes on a table in Oracle. There are several ways to achieve this, we could do the same thing by adding logic in a Java application but in this case we are going to use some of the capabilities that Oracle offers us (specifically triggers). You could use this code for security reasons or because you want to have a backup from a very important table in your application.

First of all we are going to do is to create the table we want to trace. Bear in mind this is only an example.

-- THIS IS OUR EXAMPLE TABLE
CREATE TABLE USERS (ID NUMBER PRIMARY KEY,
NAME VARCHAR2(30),
SURNAME VARCHAR2(30),
BIRTHDATE DATE);

Now we are going to create the backup table. This table must contain the same columns than the previous one and for that reason we are going to use the CTAS command. It’s important to add the clause ‘WHERE’ and the condition 1=2 if we don´t want to copy the current registries to our buckup table.

-- WE CREATE A NEW TABLE WHICH WILL STORE ALL THE CHANGES ON THE PREVIOUS TABLE
CREATE TABLE USERS_BK AS SELECT * FROM USERS WHERE 1=2;

After creating the table we are going to add some extra information. We add information about user, date type and type of operation performed as well as a new ID

-- WE ADD SOME EXTRA INFORMATION APART FROM THE DATA ITSELF
ALTER TABLE USERS_BK ADD (ID_BK NUMBER PRIMARY KEY,
USER_NAME VARCHAR2(25),
CHANGE_DATE DATE,
OPERATION CHAR);

This step is not mandatory but is highly recommendable. In this case we are going to create a sequence in order to have a unique key for all rows. It is also important because it will create an index what will improve all the searches on this table.

-- WE CREATE A NEW SEQUENCE. THIS SEQUENCE IS FOR OUR NEW BACKUP TABLE
CREATE SEQUENCE USERS_BK_SEQ START WITH 1 INCREMENT BY 1;

Now it is time of creating our procedure. Let’s explain briefly the code:

  • “CREATE OR REPLACE TRIGGER MY_TRIGGER”. We create or replace a trigger.
  • “BEFORE INSERT OR UPDATE OR DELETE ON USERS”. We are telling to Oracle we want to execute the following code for any DML operation executed on the table users. We could have used the clause ‘of’. The clause of tells Oracle the columns affected (for updating operations).
  • “FOR EACH ROW”. This tells Oracle we want to perform our code for every row. If we don´t specify this clause the code is executed just once per statement which means we could miss some registries if the user perform an instruction like “INSERT INTO USERS (QUERY)”.
  • “SELECT USER INTO U FROM DUAL”. This add to our variable the current user logged into the database.
  • “INSERTING, UPDATING, DELETEING”. This tells us what operation is being executed at the moment.
  • “:OLD and :NEW”. ‘:NEW’ refers to the new row data and ‘:OLD’ refers to the precious row data. If we are inserting ‘:OLD’ will have no value. If we are deleting then ‘:NEW’ will have no value. Only in the case of updating ‘:NEW’ will have the new values and ‘:OLD’ the previous values.
-- THIS PROCEDURE WILL RECORD ALL THE CHANGES ON USERS TABLE INTO USERS_BK
CREATE OR REPLACE TRIGGER MY_TRIGGER
BEFORE INSERT OR UPDATE OR DELETE ON USERS
FOR EACH ROW
DECLARE
U VARCHAR2(25);
BEGIN
SELECT USER INTO U FROM DUAL;
IF INSERTING THEN
INSERT INTO USERS_BK VALUES (:NEW.ID, :NEW.NAME, :NEW.SURNAME, :NEW.BIRTHDATE, USERS_BK_SEQ.NEXTVAL, U, SYSDATE, 'I');
ELSIF UPDATING THEN
INSERT INTO USERS_BK VALUES (:NEW.ID, :NEW.NAME, :NEW.SURNAME, :NEW.BIRTHDATE, USERS_BK_SEQ.NEXTVAL, U, SYSDATE, 'U');
ELSE
INSERT INTO USERS_BK VALUES (:OLD.ID, :OLD.NAME, :OLD.SURNAME, :OLD.BIRTHDATE, USERS_BK_SEQ.NEXTVAL, U, SYSDATE, 'D');
END IF;
END;

After this step is time to insert and modify some data in our ‘USERS’ table:

INSERT INTO USERS VALUES(1, 'JOHN', 'SMITH', TO_DATE('02/05/1978','DD/MM/YYYY'));
INSERT INTO USERS VALUES(2, 'SOPHIE', 'KINGSTON', TO_DATE('08/11/1976','DD/MM/YYYY'));
UPDATE USERS SET NAME='ANOTHER' WHERE ID=2;
DELETE USERS WHERE ID=1;
INSERT INTO USERS VALUES(3, 'MARC', 'HARRISON', TO_DATE('23/08/1981','DD/MM/YYYY'));

Finally we are going to retrieve the data from USERS_BK to check if our trigger is working properly

-- WE CHECK THE REGISTRIES RECORDED
SELECT ID, NAME, ID_BK, USER_NAME, CHANGE_DATE, OPERATION FROM USERS_BK;

It looks pretty good :). Hope you enjoyed this article.


triggers

Tagged on: ,

Leave a Reply

Your email address will not be published. Required fields are marked *


+ two = 11

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>