Updating Administrative Columns Using a Trigger in Oracle

Consider a table like the one below:

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)
);

Specifically, we have two columns in this table CREATE_DATE and MODIFIED_DATE that I call administrative columns.  These are here not because they have anything to do with the business domain data for the record (in this case a student), but purely for tracking purposes.  Its useful to know when a row was created and last modified.  In fact, if you are performing optimistic locking to update columns, it is imperative to know when the row was last modified, but that is a topic for a different day.

The problem we face is that we want to keep these rows accurate and in sync.  If we only create and update rows in our application, no problem.  We can make sure that our data access code or stored procedure appropriately sets these rows for each use case and we are gold.

But here is the catch.  We can't always guarantee that someone will insert or update rows from our application.

Maybe someone writes another application that needs to modify data in our database.  Yes, we would like for it to use the same data access libraries, but in the real world, we know this is not always the case.

And what about the use case for someone modifying data via their SQL Editor.  Sure, in production, things should be locked down such that this doesn't happen often, but inevitably, it will need to happen at some point.  Some rows will get messed up and someone will need to go in and fix them.

And so our challenge is how do we make sure these columns are always correct and always updated no matter what the use case is when they are inserted or updated to.

This is a place where a trigger can really help.

Consider the following:

CREATE OR REPLACE TRIGGER tr_students_create_mod_date 
    BEFORE INSERT OR UPDATE
    ON students
    FOR EACH ROW
    BEGIN
    CASE
        WHEN INSERTING THEN
            :new.create_date := sysdate;
            :new.modified_date := sysdate;
        WHEN NOT UPDATING THEN
            :new.create_date := :old.create_date;
            :new.modified_date := sysdate;        
    END CASE;
END;

This is a very simple trigger.  When a row is being inserted, it sets the value of CREATE_DATE and MODIFIED_DATE to the current server date (sysdate).  When a row is being updated, the MODIFIED_DATE is set to sysdate, but the CREATE_DATE is set to the original create date.  The effect this has is that even if someone tries to change the CREATE_DATE for a row after the row is inserted, it won't matter.  We'll always preserve that original CREATE_DATE.

And so this is one of the little niceties that make our life easier.  We don't have to include these values in our statements and we can be assured that they will always be correct, no matter how a row is inserted or updated in our database.