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;