Triggering Thoughts on PostgreSQL Triggers

This post is a response to Lætitia Avrot’s triggering prompt for PGSQL Phriday #007. PostgreSQL is one of the many database technologies we encounter on our projects, so I wanted to share some thoughts on Triggers in Postgres.

What are triggers?

Triggers are SQL functions that are executed when an operation happens. The SQL function is created first, then it can be tied to a triggering event via the CREATE TRIGGER statement.

PostgreSQL has row-level and statement-level triggers. Row-level triggers will trigger their functions for each row impacted by an operation. These are indicated by FOR EACH ROW in their creation. Statement-level triggers are only run once and will run even if there are 0 rows affected. These are indicated by FOR EACH STATEMENT in their creation.

Some triggering events include:

• Before or after INSERT, UPDATE, or DELETE statements on tables
• On TRUNCATE statements for tables
• Instead of INSERT, UPDATE, or DELETE on views

Learn how to create triggers in PostgreSQL.

Examples of triggers

Here are a few use cases of triggers.

Audit trails

Audit trails are a common use for triggers. These track who made what changes to the data with a timestamp of when it happened. These are commonly implemented as row-level triggers after INSERT, UPDATE, and DELETE statements. They can also be implemented for TRUNCATE statements in a statement-level trigger.

Note: TRUNCATE removes all the data from the table - it does not DELETE the data. Therefore, TRUNCATE does not trigger DELETE triggers. You must use a BEFORE TRUNCATE or AFTER TRUNCATE trigger to capture truncates in an audit trail.

Syncing data

There are times where data may need to be repeated in a database. When that’s the case, one table is known as the source of truth and triggers can be used to copy the relevant data to the other tables.

Data validation

BEFORE triggers can be used to prevent data from being written if the data doesn’t meet certain validation rules. These triggers run before the statements are executed, so if the validation fails, the data does not get saved to the database. Data validation in the database can also be considered one of the pains behind triggers.

The Pain Behind Triggers

In dealing with various databases over the past 2 decades, triggers have always been a point of contention for both developers and database administrators.

Battle of business logic

One of the problems with triggers is the battle for business logic. Those who work primarily at the database level may fight for keeping business logic in functions, stored procedures, and triggers. Developers who work primarily in application development languages may argue that the business logic lives in their applications. In some organizations, I have seen business logic in both the database code and the application code - and now both teams are responsible for tracking where that logic lives and maintaining it whenever the business changes directions.

Forgotten triggers

Another problem with triggers is that they are not always at the forefront of a developer’s mind. When these are not on the radar of development, there could be unintended consequences.

Complex triggers

Triggers can have nested logic within them. This can lead to triggers calling functions that call more functions that can lead to performance issues. Imagine impacting millions of rows and then triggering a per-row trigger. A simple audit trail trigger may be quick. However, a validation trigger may not necessarily scale as well and may lead to a heavy load on the server.

Triggers in a distributed environment

Another concern with triggers is how they operate in a scaled environment. If you are working with a distributed PostgreSQL environment - such as a self-hosted Citus environment or Azure Cosmos DB for PostgreSQL, triggers on distributed tables are tricky.

Conclusion

Do we love triggers? Do we hate triggers? Do we have a stance on triggers? I’m going to take the stance of not having feelings either way for triggers in general.

Triggers are another tool in a database developer’s toolbox with very specific purposes. However, they have their limits and should be used appropriately. Keep them simple. If you are running into performance issues from a trigger, you need to consider whether the trigger is the right approach and can be improved in a way to efficiently and effectively meet your needs.

When it comes to working with developers and triggers, document the triggers and share that documentation with the developers. Clear documentation and communication have saved me many headaches when dealing with database administrators as a developer and as a database administrator dealing with developers.

We aren’t the only ones talking about triggers. Be sure to check out the #PGSQLPhriday hashtag on Twitter and see what others have to say about triggers in Postgres!

If you are using PostgreSQL with .NET and would like us to help out with your code, contact us!