skip to content

SQL: PostgreSQL trigger for updating last modified timestamp

 Tweet0 Shares0 Tweets

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 it's 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 now() );

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:

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 != OLD.password THEN NEW.password_changed := current_date; END IF; RETURN NEW; END; $$;

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'::regclass) username | character varying | not null password | character varying | not null password_changed | date | default now() 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 != 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 != 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 2017-07-23

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

< SQL

Send a message to The Art of Web:


used only for us to reply, and to display your gravatar.

<- copy the digits from the image into this box

press <Esc> or click outside this box to close

Post your comment or question
top