Trigger Warning! Using SQL Triggers to Track Database Changes in Postgres

Table of contents

What is a SQL Trigger?

SQL Triggers, also called Database Triggers, allow you to tell your SQL engine (for these examples, Postgres) to run a piece of code when an event happens, or even before the event.

Advantages of using SQL Triggers

Maintaining data integrity. Database triggers have a variety of uses and are an excellent tool to marshal strict data integrity. Alternate solutions like Django’s model hooks may fail if you have other application servers or users accessing the database who aren’t aware of the specific business logic coded in your application.

How to Create a SQL Trigger — Postgres Syntax

Here are the components to creating a trigger for your database:

  1. Before or After the event
  2. Effect of the Trigger
Trigger Warning! Guide to SQL Triggers - Setting up Database Tracking and Auditing in PostgreSQL
SQL Trigger Anatomy

Trigger event types

Database triggers will monitor tables for specific events. Here are some examples of different events that can activate a trigger:

Trigger BEFORE or AFTER

A trigger can run either BEFORE or AFTER an event.

Effect of the trigger

A trigger can run either per row, or per statement. Let’s say you run a single UPDATE statement that changes 5 rows in a table.

Postgres Trigger Example #1: Creating a Time Clock

A time clock records when an employee comes and leaves from work and calculates his/her total hours worked. Let’s create an example time clock and look at how we can use triggers to prevent employees from inputting invalid data.

Setting up the DB schema

The design of this schema treats each punch in and out as separate events. Each event is a row in the time_punch table. Alternatively, you could also make each employee" shift" an event and store both the punch in and punch out time in one row.

create table employee (
id serial primary key,
username varchar
);

create table time_punch (
id serial primary key,
employee_id int not null references employee(id),
is_out_punch boolean not null default false,
punch_time timestamp not null default now()
);

insert into employee (username) values ('Bear');
insert into time_punch (employee_id, is_out_punch, punch_time)
values
(1, false, '2020-01-01 10:00:00'),
(1, true, '2020-01-01 11:30:00');

Using SQL to calculate time worked

The solution I decided on looks at each “out” punch and matches it with its preceding “in” punch.

select tp1.punch_time - tp2.punch_time as time_worked
from time_punch tp1
join time_punch tp2
on tp2.id = (
select tps.id
from time_punch tps
where tps.id < tp1.id
and tps.employee_id = tp1.employee_id
and not tps.is_out_punch
order by tps.id desc limit 1
)
where tp1.employee_id = 1
and tp1.is_out_punch
time_worked
-------------
01:30:00
(1 row)

SQL INSERT BEFORE trigger example - preserving data integrity

We need something to prevent the in/out pattern from being interrupted. Unfortunately check constraints only look at the row being inserted or updated and cannot factor in data from other rows.

create or replace function fn_check_time_punch() returns trigger as $psql$
begin
if new.is_out_punch = (
select tps.is_out_punch
from time_punch tps
where tps.employee_id = new.employee_id
order by tps.id desc limit 1
) then
return null;
end if;
return new;
end;
$psql$ language plpgsql;
create trigger check_time_punch before insert on time_punch for each row execute procedure fn_check_time_punch();
insert into time_punch (employee_id, is_out_punch, punch_time)
values
(1, true, '2020-01-01 13:00:00');
Output:
INSERT 0 0

Postgres Trigger Example #2: Creating an Audit Table

Accurately storing employee punch data is critical for businesses. This type of data often ends up directly translating to an employee’s salary, and on the other end, a company’s payroll cost.

Create the audit table

There are several ways to keep an audit or history table. Let’s create a separate table that will store the past states of time_punch.

create table time_punch_audit (
id serial primary key,
change_time timestamp not null default now(),
change_employee_id int not null references employee(id),
time_punch_id int not null references time_punch(id),
punch_time timestamp not null
);
  • Employee who updated it
  • ID of the punch that was changed
  • Punch time before the punch was updated
alter table time_punch add column change_employee_id int null references employee(id);

SQL UPDATE AFTER trigger example - inserting data

After an update happens to our time_punch table, this trigger runs and stores the OLD punch time value in our audit table.

create or replace function fn_change_time_punch_audit() returns trigger as $psql$
begin
insert into time_punch_audit (change_time, change_employee_id, time_punch_id, punch_time)
values
(now(), new.change_employee_id, new.id, old.punch_time);
return new;
end;
$psql$ language plpgsql;
create trigger change_time_punch_audit after update on time_punch
for each row execute procedure fn_change_time_punch_audit();
select punch_time
from time_punch
where id=2;
punch_time
---------------------
2020-01-01 11:30:00
(1 row)
update time_punch set punch_time = punch_time + interval '5 minute', change_employee_id = 2 where id = 2;
change_time         | username |     punch_time      
----------------------------+----------+---------------------
2021-01-06 20:10:56.44116 | Daniel | 2020-01-01 11:35:00
2021-01-06 20:10:55.133855 | Daniel | 2020-01-01 11:30:00

Additional Considerations for Triggers

There are a few things to be wary of with database triggers:

  1. Connected trigger logic
  2. Developer expertise

Using Triggers to Level Up Your SQL Game

I hope that these examples have helped you develop a better understanding of database triggers. I have had great successes using triggers for data problems where traceability, consistency, and accuracy are paramount.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Arctype

Arctype

The modern SQL editor built for collaboration www.arctype.com