How To Use PostgreSQL Triggers for Automated Processing of Data

Database triggers are a way to run a piece of code when a predefined operation occurs on the database. You can relate to triggers as event handlers in javascript. In javascript, you can set up an event handler for a button click. Database triggers are just like that.

In this article, we'll go through the basics of setting up triggers in PostgreSQL and a few examples to see how they can be used. Triggers can be created for data changes or database events. We'll be looking at data changes i.e., triggers that run BEFORE/AFTER INSERT, UPDATE, DELETE operations on a table.

The article is divided into the following sections:

• What is a trigger
• Types of triggers
• Trigger function
• 2 steps to write a trigger in PostgreSQL
• Data visibility in triggers
• Examples
• Drop trigger
• Conclusion

What is a trigger

Taking from the docs, a trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed. We can use triggers on tables (partitioned or not), views, and foreign tables.

Triggers can be defined to execute either before or after any INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement.

A trigger must have a trigger function that runs whenever a supported operation occurs. Creating a trigger includes providing the trigger function. It takes no arguments and returns the type trigger.

Types of triggers

There are a lot variations in how a trigger gets invoked. Let's look at them:

1. Row level triggers
2. Statement level triggers

Row-level triggers run for each row in the table whereas statement-level triggers run once per transaction. For example, if a single transaction updates a thousand rows then the statement-level trigger will only run once but the row-level trigger will run a thousand times.

Triggers are also classified according to whether they fire before, after, or instead of the operation. These are referred to as BEFORE triggers, AFTER triggers, and INSTEAD OF triggers respectively.

For per-row triggers:

• The return value is ignored for row-level triggers fired AFTER an operation so they can return NULL.
• BEFORE level trigger can return NULL to skip the operation for the current row. This instructs the executor to not perform the row-level operation that invoked the trigger (the insertion, modification, or deletion of a particular table row).
• For row-level BEFORE INSERT/UPDATE triggers, the returned row becomes the row that will get inserted or updated. It allows the trigger function to modify the row being inserted or updated.
• Trigger functions invoked by per-statement triggers should always return NULL.

Trigger function

A trigger function is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger for data change triggers. Special local variables named TG_something are automatically defined to describe the condition that triggered the call.

Trigger functions can be written in most of the procedural languages including PL/pgsql, Perl, Python. With PL/pgSQL function as a trigger, there are some variables automatically created at the top-level block.

Note that the function must be declared with no arguments even if it expects to receive some arguments specified in CREATE TRIGGER. Such arguments are passed via TG_ARGV.

We'll take a look at some of the common ones:

NEW

Holds the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations.

OLD

Holds the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations.

TG_WHEN

A string of BEFORE, AFTER, or INSTEAD OF, depending on the trigger's definition.

TG_LEVEL

Indicates whether trigger is ROW or STATEMENT level.

TG_OP

Shows the operation that triggered the trigger. Can be INSERT, UPDATE, DELETE, or TRUNCATE.

TG_ARGV[]

The arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.

2 steps to write a trigger in PostgreSQL

A fully functioning trigger in PostgreSQL can be created in just two steps:

1. Define a trigger function
2. Create the trigger using that trigger function

We'll create a trigger function first which can be used by a trigger:

1CREATE OR REPLACE FUNCTION function_name()
2  RETURNS trigger
3  LANGUAGE plpgsql
4AS $$
5DECLARE
6  -- declare variables if needed
7BEGIN
8  -- function body goes here
9END;
10$$;

Now that we have the trigger function, we can create the trigger:

1CREATE OR REPLACE TRIGGER name
2  { BEFORE | AFTER } { event }
3  ON table_name
4  [ FOR [ EACH ] { ROW | STATEMENT } ]
5  [ WHEN ( condition ) ]
6  EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

CREATE OR REPLACE TRIGGER will either create a new trigger or replace an existing trigger. If you don't want that, use CREATE TRIGGER instead. It won't create the trigger if it already exists.

The trigger will be associated with the specified table and will execute the specified trigger function when certain operations are performed on that table BEFORE/AFTER the operation has happened.

You can also put conditions on when the trigger should fire using the WHEN clause. In row-level triggers, the WHEN condition can examine the old and/or new values of columns of the row. It is not useful in the case of statement-level triggers because they cannot refer to any values in the table.

Some use-cases for different triggers

Row-level BEFORE triggers are used for checking or modifying the data that will get inserted or updated. For example, a BEFORE trigger might get used to insert the current time into a timestamp column or to check that two elements of the row are consistent. It can also act as a validation stage where you reject the unwanted data (eg. null checks).

Row-level AFTER triggers are often used to propagate the updates to other tables or make consistency checks against other tables. Reason? AFTER trigger always sees the final value of the row while a BEFORE might not. There might be other BEFORE triggers firing after it.

If possible, using the BEFORE trigger is better from a performance standpoint. We shouldn't save the information about the operation until the end of a statement if not needed.

Data visibility in triggers

There are many use-cases for triggers and accessing the new or old values of a row in a table is one of them. The ability to look into the data allows us to perform various actions that wouldn't be possible otherwise. Audit logs, check constraints, and version history are some of the common and straightforward use-cases.

Keeping this in mind, it is important to know when we can access the old and new data of a row in the table.

• In a statement-level trigger, none of the changes are visible to BEFORE triggers, whereas all changes are visible to AFTER triggers.
• Any modifications to the data that happen during INSERT, UPDATE, DELETE is not visible to BEFORE level triggers because the change hasn't happened yet.
• BEFORE trigger can see the effects of previous runs of BEFORE triggers.
• All the changes are visible to AFTER triggers because the change has already happened.

Examples

Let's take a look at some examples showing different use cases of triggers. These are very straightforward examples, but they are not the only use cases.

Checking constraints

We can set a check constraint on a table to ensure that certain column values are not null or have certain values. This can be achieved with the help of a BEFORE INSERT trigger.

1CREATE OR REPLACE FUNCTION name_null_checker() RETURNS trigger
2LANGUAGE plpgsql
3AS $$
4BEGIN
5    IF NEW.name IS NULL THEN
6        RAISE EXCEPTION 'Name cannot be null';
7    END IF;
8    RETURN NEW;
9END;
10$$;

We have written a basic trigger function that checks if the name of the book is null and throws an exception if it is. With this step, we have created a re-usable trigger function that can be used by multiple triggers to check the name constraint. Properly written, this trigger function would be independent of the specific table it is triggering on.

Now, let's create a trigger that will fire BEFORE an INSERT operation on the books table.

1CREATE TRIGGER name_null_checker_trigger BEFORE INSERT ON books
2FOR EACH ROW EXECUTE FUNCTION name_null_checker();

And now we have a basic trigger in place to perform constraint check on books table.

Audit logging

Audit logs are a way to keep track of changes to a table. We will set up a trigger to log all INSERT, UPDATE and DELETE on the books table.

We need to create a books audit table to store the audit logs. Let's first create it:

1CREATE TABLE IF NOT EXISTS books_audit_store (
2  name VARCHAR(128) UNIQUE NOT NULL,
3  price float(2) NOT NULL,
4  rating INT NOT NULL,
5  operation VARCHAR(64) NOT NULL,
6  modified_at TIMESTAMP NOT NULL
7);

Now that we have our books_audit_store table we can create the trigger function which will perform the INSERT SQL command:

1CREATE OR REPLACE FUNCTION audit_logger()
2  RETURNS trigger
3  LANGUAGE plpgsql
4AS $$
5BEGIN
6  INSERT INTO books_audit_store (name, price, rating, operation, modified_at) 
7    VALUES (NEW.name, NEW.price, NEW.rating, TG_OP, now());
8  RETURN NEW;
9END;
10$$;

Finally, creating the trigger on books_audit_store table:

1CREATE TRIGGER audit_logger_trigger
2AFTER UPDATE ON books
3FOR EACH ROW
4WHEN (OLD.* IS DISTINCT FROM NEW.*)
5EXECUTE FUNCTION audit_logger();

Here's the books table before update:

We've updated the "Scrum" book title to be "Scrum master":

As a result of the update on books table with a different value, we got our audit log entry in books_audit_store table:

Drop trigger

A trigger can be dropped by issuing a DROP TRIGGER statement.

1DROP TRIGGER triggerName ON tableName;

To drop the trigger, the user must be the owner of the table on which the trigger is defined.

We can add IF EXISTS to the statement to drop the trigger only if it exists:

1DROP TRIGGER IF EXISTS triggerName ON tableName;

Conclusion

This is just the introduction to triggers and how you can use them with functions to create post processors, validators, and audit loggers. The possibilities are endless, going through the official docs of CREATE TRIGGER would be a good next step. It has some important notes and examples which you can explore.

I'd be writing about more extensive applications of triggers once this information fits better with my current understanding of databases. Just connecting the dots would be a good start.