SQL: PostgreSQL trigger for updating last modified timestamp

Tweet 0 Shares 0 Tweets 0 Comments

Database triggers are a handy way to monitor database activity and to efficiently perform some maintenance tasks. In the following example we want a database table (users) to keep track of when one of its fields (password) was last updated.

Background

We have a simple database of usernames and (encrypted) passwords with the following definition:

CREATE TABLE users (
    id serial,
    username varchar UNIQUE NOT NULL,
    password varchar NOT NULL,
    password_changed date DEFAULT CURRENT_DATE,
    PRIMARY KEY (id)
);

What we want is for the password_changed field to be updated automatically whenever an UPDATE is used to change a users password.

In practice this could occur when:

the user changes their password;
the webmaster assigns the user a new password; or
the sysadmin updates their password from the command-line or another script.

Rather than tackling each of these independently, using a trigger we can address them all at the same time, and all from within the database.

Creating the trigger FUNCTION

We create our trigger function using PL/pgSQL - which comes built in in PostgreSQL 9.0 and later, and can be installed as an add-on for earlier versions.

CREATE FUNCTION password_changed() RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
BEGIN
  IF NEW.password IS DISTINCT FROM OLD.password THEN
    NEW.password_changed := CURRENT_DATE;
  END IF;
  RETURN NEW;
END;
$$;

Note that we have used IS DISTINCT FROM rather than the common != here as the latter has problems handling NULL values.

By returning 'NEW' we are forcing any UPDATE which includes a new value for password to also update the password_changed field to today's date.

Creating the TRIGGER

A trigger function by itself doesn't do anything unless it's called by a TRIGGER event. In this case we want a BEFORE UPDATE trigger to intercept the UPDATE command:

CREATE TRIGGER trigger_password_changed
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE PROCEDURE password_changed();

Now every UPDATE on the users table will be routed through our trigger function, and if it contains a new (encrypted) password then the password_changed date field will also update.

VIEWing the trigger details

We can see the schema of the users table using:

# \d users
                                   Table "public.users"
     Column       |       Type        |                     Modifiers
------------------+-------------------+----------------------------------------------------
 id               | integer           | not null default nextval('users_id_seq')
 username         | character varying | not null
 password         | character varying | not null
 password_changed | date              | default CURRENT_DATE
Indexes:
    "users_username_key" UNIQUE CONSTRAINT, btree (username)
Triggers:
    trigger_password_changed BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE password_changed()

And taking the function name from the last line, we can also view the source code behind our trigger:

# \df+ password_changed
List of functions
-[ RECORD 1 ]-------+-----------------------------------------
Schema              | public
Name                | password_changed
Result data type    | trigger
Argument data types |
Type                | trigger
Security            | invoker
Volatility          | volatile
Owner               | postgres
Language            | plpgsql
Source code         |
                    | BEGIN
                    |   IF NEW.password IS DISTINCT FROM OLD.password THEN
                    |     NEW.password_changed := CURRENT_DATE;
                    |   END IF;
                    |   RETURN NEW;
                    | END;

Because the trigger is defined within the database it will survive any normal backup and restore process unaffected.

Raising a NOTICE

To make things easier for anyone maintaining the database we can add a reporting function for when the TRIGGER makes changes:

CREATE OR REPLACE FUNCTION password_changed() RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
BEGIN
  IF NEW.password IS DISTINCT FROM OLD.password THEN
    NEW.password_changed := CURRENT_DATE;
    RAISE NOTICE 'Password changed for user ''%'' on %', OLD.username, NEW.password_changed;
  END IF;
  RETURN NEW;
END;
$$;

Now whenever a password is updated you will see a NOTICE:

# UPDATE users SET password='abc123' WHERE username='nobody';
NOTICE:  Password changed for user 'nobody' on 2024-04-25

And we're done.

An alternative approach

This version should be slightly more efficient because it only calls the trigger function when the password has changed:

CREATE TABLE users (
    id serial,
    username varchar UNIQUE NOT NULL,
    password varchar NOT NULL,
    password_changed date DEFAULT CURRENT_DATE,
    PRIMARY KEY (id)
);

CREATE OR REPLACE FUNCTION password_changed() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  NEW.password_changed := CURRENT_DATE;
  RAISE NOTICE 'Password changed for user ''%'' on %', OLD.username, NEW.password_changed;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trigger_password_changed
  BEFORE UPDATE ON users
  FOR EACH ROW
    WHEN (OLD.password IS DISTINCT FROM NEW.password)
  EXECUTE PROCEDURE password_changed();

Previously the trigger function was called for every UPDATE, and then had to check for itself whether the password was changing. Now it's called only when the password actually is changing.

References

PostgreSQL: PL/pgSQL - SQL Procedural Language
PostgreSQL: Trigger Procedures

< SQL

Post your comment or question