Archiving Rows in Oracle Using a Trigger

Often times, we are faced with the situation that any time a row is updated or deleted in a table, we want to store the previous version of the row in an archive table.  In some cases this is a requirement for audit purposes. Even in cases where it is not required for audit, it is still quite nice to be able to go back and look what a row was on a certain date and to know when the value last changed.

For example, several places I have worked have kept application settings in a table in the database.  And I've run into situations where a value was updated and all of the sudden things started to go wrong.  It is those times I wished that we had some sort of archiving mechanism so we could easily tell what changed, when, and what the previous values were.

This is something that can be easily accomplished with a trigger.  And the nice thing about the trigger solution is that this happens transparently in the background.  It doesn't matter if a row is updated by application code or an ad-hoc query in a SQL Editor, the prior version of the row will get archived.  So lets see how this works.

First, consider that we have the following data table.

CREATE TABLE students
(
    student_id      NUMBER(10)      GENERATED ALWAYS AS IDENTITY NOT NULL,
    first_name      VARCHAR2(30)    NOT NULL,
    last_name       VARCHAR2(30)    NOT NULL,
    email           VARCHAR2(50)    NULL,
    phone           VARCHAR2(20)    NULL,
    create_date     DATE            NOT NULL,
    modified_date   DATE            NOT NULL,
    CONSTRAINT pk_students PRIMARY KEY (student_id)
);

Then, we have this as an archive table.  Note, I am not enforcing a foreign key on the STUDENT_ID column back to the STUDENTS table from STUDENTS_ARCHIVE.  If we deleted a row from the STUDENTS table, then there would be no parent key to reference, so this would not work.

CREATE TABLE students_archive
(
    archive_id          NUMBER(10)      GENERATED ALWAYS AS IDENTITY NOT NULL,
    student_id          NUMBER(10)      NOT NULL,
    first_name          VARCHAR2(30)    NOT NULL,
    last_name           VARCHAR2(30)    NOT NULL,
    email               VARCHAR2(50)    NULL,
    phone               VARCHAR2(20)    NULL,
    create_date         DATE            NOT NULL,
    active_start_time   DATE            NOT NULL,
    active_end_time     DATE            NOT NULL,
    operation_type      VARCHAR2(1)     NOT NULL,
    CONSTRAINT pk_students_archive PRIMARY KEY (archive_id)
);

And now for the trigger.  Its quite simple really.

CREATE OR REPLACE TRIGGER tr_students_archive_row
    AFTER UPDATE OR DELETE    
    ON students    
    FOR EACH ROW        
DECLARE 
        op_type  VARCHAR2(1);
BEGIN
    CASE
        WHEN UPDATING THEN
            op_type := 'U';          
        WHEN DELETING THEN
            op_type := 'D';
    END CASE;
    
    INSERT INTO students_archive (student_id, first_name, last_name, 
        email, phone, create_date, 
        active_start_time, active_end_time, operation_type)
    VALUES (:old.student_id, :old.first_name, :old.last_name, 
        :old.email, :old.phone, :old.create_date, 
        :old.modified_date, sysdate, op_type);
    
END;

The trigger fires AFTER EACH ROW on an UPDATE or DELETE statement.  I fire AFTER EACH ROW because of course I want to archive every row affected and also because I want to make sure the UPDATE or DELETE was successful.

Then, I use a CASE...WHEN statement to put a code into a variable indicating if an UPDATE or DELETE operation is being performed.

Finally, I insert the OLD data (via the :OLD) pseudorecord into the archive table.  The time this version the record started being active is the modified_date on the old version of the record, and the time it stopped being active is right now.  In this case, I am using DATE values, but you just as well could use TIMESTAMP values for more accuracy.

And that is it.  Now, whenever a record is changed or deleted in our data table, we'll have a history of it in our archive table.  It is a very clean solution to the problem.

If the updates and deletes against your table are mostly of the single row variety, then this trigger will work great for you.  If you are doing a lot of bulk updates against the data in your table, then a compound trigger will likely offer better performance, which I'll cover in a future blog post.