Automatically Setting Modified Date Database Fields

Most of my database tables always have four columns in common:

created_on timestamp
created_by_id int
last_modified_on timestamp
last_modified_by_id int

These columns should be pretty self explanatory. Usually created_on has a default of now() to auto populate it. Populating the last_modified_on column is a little more difficult, especially if you don’t want it to be populated on INSERT (which is what using default now()) would do.

Of course we could just provide for it in our code, especially if using a trusty application framework that takes care of it for us, but I like putting basic logic in the database, and we can’t assume all data updates will come from our application. They could, for example, come from a command line update.

I crafted this quick little database trigger function for taking care of this for me:

CREATE OR REPLACE FUNCTION auto_update_last_modified()
  RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
	NEW.last_modified_on:= now();
 
	RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql';

This function simply populates the last_modified_on column of NEW (the new record being updated) with the current timestamp. Now I can add a trigger to every table that has this column:

CREATE TRIGGER auto_update_table_name_last_modified
  BEFORE UPDATE
  ON table_name
  FOR EACH ROW
  EXECUTE PROCEDURE auto_update_last_modified();

And now when updating a record in table_name, it will automatically populate a value for last_modified_on.