DB Triggers

I’ve been doing IT for more than 15 years. There are some tools that I’ve always heard of but never really found worth the effort to investigate as they have never fully seemed like good practice. Still, the more tools you have available the more flexible you are to adress diverse problems. So that’s why I’ll start doing some investigations to just explore what’s possible if you use some of the deeper features around.

Today’s attemp will be with DB Triggers. So what are they? Basically it’s something that gives you the ability to trigger SQL code when operations happen to a table. Be it that an entry is updated, created or deleted. One of the obvious reasons for this feature is that it allows you to do auditing. Since this logic is written in the DB itself, there is little to no risk that you accidentally miss writing the audit log in some path of your code.

So let’s look at it. We use Postgres for this example. If you want to follow along and have docker you can run the following command in a terminal (if you use mac or linus).

docker run -it -e POSTGRES_PASSWORD=postgres -p5432:5432 --rm postgres:12

Open another terminal and run

psql -h localhost -U postgres

to be able to write commands.

First we create a table for users:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,  -- Consider using a secure hash function, not plain text.
    isAdmin BOOLEAN DEFAULT FALSE     -- Default value set to FALSE
);

We insert some data.

INSERT INTO users (username, password, isAdmin) VALUES ('john_doe', 'hashedPassword1', FALSE);
INSERT INTO users (username, password, isAdmin) VALUES ('jane_smith', 'hashedPassword2', TRUE);
INSERT INTO users (username, password, isAdmin) VALUES ('alice_jones', 'hashedPassword3', FALSE);
INSERT INTO users (username, password, isAdmin) VALUES ('bob_brown', 'hashedPassword4', TRUE);
INSERT INTO users (username, password, isAdmin) VALUES ('charlie_white', 'hashedPassword5', FALSE);

Now we create an audit table where you can save data

CREATE TABLE users_audit (
    audit_id SERIAL PRIMARY KEY,
    user_id INT,
    old_username VARCHAR(255),
    new_username VARCHAR(255),
    old_isAdmin BOOLEAN,
    new_isAdmin BOOLEAN,
    changed_by VARCHAR(255),
    change_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

We create a function that can be triggered by the change on the users table:

CREATE OR REPLACE FUNCTION log_isAdmin_change() 
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.isAdmin IS DISTINCT FROM NEW.isAdmin THEN
        INSERT INTO users_audit(user_id, old_username, new_username, old_isAdmin, new_isAdmin, changed_by)
        VALUES (NEW.id, OLD.username, NEW.username, OLD.isAdmin, NEW.isAdmin, CURRENT_USER);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

We activate the trigger on the users table

CREATE TRIGGER tr_users_isAdmin_changed
AFTER UPDATE ON users
FOR EACH ROW 
WHEN (OLD.isAdmin IS DISTINCT FROM NEW.isAdmin)
EXECUTE FUNCTION log_isAdmin_change();

Now try to update an entry, and see what you can see in the users_audit table.

And that’s it of today’s shallow dive on a feature I don’t use all that often.