This post is part of an impromptu series about PostgreSQL and things I am learning coming from a SQL Server background. A complete list of posts can be found on this post.
While Triggers have gotten a bad rap in many circles and are often discouraged (great read by Joe Celko), they are still a useful tool in a handful of situations. Trying to implement my first Triggers in PostgreSQL has been one of many learning adventures lately. Here are a few key points I want to remember for next time and share with others.
They are implemented as Functions (Stored Procedures)
This caught me off guard at first. I’ve been working with and dealing with triggers in SQL Server since day 1. They are first-class citizens… objects that have their own code blocks and rules.
PostgreSQL approaches it differently. Any reusable code block, regardless of its true purpose is a Function of varying types. Triggers are no different. Therefore, you write the logic of your trigger in a Function and then call it by adding a trigger to the DML event of a table.
CREATE OR REPLACE FUNCTION user_log() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO UserLog (....) END; $BODY$ LANGUAGE plpgsql;
Once the function is available, you “attach” it to the DML event of the table by creating the trigger.
CREATE TRIGGER ti_users AFTER INSERT ON Users FOR EACH ROW EXECUTE PROCEDURE user_log();
Triggers can be run per-row
Again, this threw me a little coming from SQL Server which executes triggers on a batch of rows, even if it’s only one row. This also means that when a trigger is the best place to check or modify data in an application, additional work often has to be done to iterate the data inside of the trigger. In PostgreSQL these batch-level triggers are called a statement triggers and don’t provide the same access to the batch of rows that were inserted/modified that SQL Server does with the
DELETED.* special tables
With row-level triggers, however, the function you name is called on every row that is modified. Obviously this comes with it’s own caution as well, but in the right circumstances it means that the data is easily accessible because the function is working on one row of data at a time. Row-level triggers also give you access to the row data that you’re used to, but in PostgreSQL you refer to the
CREATE OR REPLACE FUNCTION user_log() RETURNS TRIGGER AS $BODY$ BEGIN INSERT INTO UserLog (Username, Message) VALUES (NEW.Username, NEW.Message) END; $BODY$ LANGUAGE plpgsql;
Triggers can have predicates!
This is actually one of the few things I like about triggers in PostgreSQL that SQL Server doesn’t have. While it might add confusion, it also makes it very easy to limit when the trigger function is actually called.
In SQL Server, the appropriate trigger is always called when the DML event is performed. We often have to jump through a bunch of hoops (SELECT… EXCEPT) to see if the data we care about has changed before we do some kind of work.
In PostgreSQL it is so much easier. When you create the trigger that attached the function to the table DML, you add the appropriate predicate(s).
CREATE TRIGGER tu_users AFTER UPDATE ON Users FOR EACH ROW WHEN (OLD.FirstName IS DISTINCT FROM NEW.FirstName) EXECUTE PROCEDURE user_log();
It’s that simple, and you can add more predicates to the WHEN clause. They just all have to be Boolean in nature.
It’s not all bad
In the end, I’m still missing most of what SQL Server offers in power, tooling and evolving technology. That said, Triggers in PostgreSQL has been one of the bright spots when they’re the right tool for the job.
3 thoughts on “PostgreSQL for a SQL Server DBA: Triggers”
One of the other things you can do with functions as triggers is have the _same_ function attached as a trigger to different tables, but with different parameters.
We use this with our auditing trigger: we can omit specific columns (like updated_by, or updated_at, since those are also populated in the audit log itself), or have other parameters that trigger (oops) different behaviour.
Great feedback. I’m actually just beginning the process of recording a Pluralsight course on SQL Server Triggers and Functions and one of the examples is the (often used) auditing trigger. It never occurred to me that PostgreSQL allows for these to be reused in a (generally) simpler fashion. Really interesting use case that requires more thought.