“Developers, developers, developers, …” - let us not remind ourselves of this painful Microsoft presentation which was given by Mr Ballmer some time ago. But what happens if we deploy triggers, triggers, triggers in PostgreSQL? And what happens if we deploy them all on the very same table? That's an interesting question, and important to consider when writing triggers. What order do the multiple triggers fire in? Let's understand trigger execution order, but first, the syntax.
Table of Contents
CREATE TRIGGERThe CREATE TRIGGER command has become really powerful over the years. It offers a ton of functionality which we can use to leverage the power of our applications, here's the syntax from the documentation:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
postgres=# h CREATE TRIGGER Command: CREATE TRIGGER Description: define a new trigger Syntax: CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] } ON table_name [ FROM referenced_table_name ] [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ] [ FOR [ EACH ] { ROW | STATEMENT } ] [ WHEN ( condition ) ] EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments ) where event can be one of: INSERT UPDATE [ OF column_name [, ... ] ] DELETE TRUNCATE |
As you can see, PostgreSQL will call a function when a trigger is executed. The beauty here is that PostgreSQL allows us to deploy as many triggers as we want on the very same table. The logical question then is: In which order are triggers executed? Can we expect deterministic execution order, or are things simply random?
The good news is: triggers in PostgreSQL are not random. They follow a certain execution order.
PostgreSQL first executes statement-level
BEFOREtriggers, then row-levelBEFOREtriggers, then row-levelAFTERtriggers and finally statement-levelAFTERtriggers. If more than one trigger in the same category exists, they are executed in alphabetical order according to the trigger name.
(this holds true since PostgreSQL 7.3, which is around 20 years old).
In short: We can rely on a deterministic order.
The following code shows a basic example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
CREATE TABLE t_demo (id int); CREATE FUNCTION trig_func() RETURNS trigger AS $ BEGIN RAISE NOTICE 'name of trigger: %', TG_NAME; RETURN NEW; END; $ LANGUAGE 'plpgsql'; CREATE TRIGGER c_trigger_bef BEFORE INSERT ON t_demo FOR EACH ROW EXECUTE PROCEDURE trig_func(); CREATE TRIGGER b_trigger_bef BEFORE INSERT ON t_demo FOR EACH ROW EXECUTE PROCEDURE trig_func(); CREATE TRIGGER a_trigger_bef BEFORE INSERT ON t_demo FOR EACH ROW EXECUTE PROCEDURE trig_func(); CREATE TRIGGER c_trigger_aft AFTER INSERT ON t_demo FOR EACH ROW EXECUTE PROCEDURE trig_func(); CREATE TRIGGER b_trigger_aft AFTER INSERT ON t_demo FOR EACH ROW EXECUTE PROCEDURE trig_func(); |
What happens here is that we have a ton of triggers on the same table. It's also important to see that we deploy the triggers in non-alphabetical order (we can deploy them in any order but to prove the point we used the “wrong” order).
|
1 2 3 4 5 6 7 |
postgres=# INSERT INTO t_demo VALUES (1); NOTICE: name of trigger: a_trigger_bef NOTICE: name of trigger: b_trigger_bef NOTICE: name of trigger: c_trigger_bef NOTICE: name of trigger: b_trigger_aft NOTICE: name of trigger: c_trigger_aft INSERT 0 1 |
The most important observation is that PostgreSQL does not care about the deployment order. As stated before, the entire order is fully deterministic. The TG_NAME variable which is set in every trigger by default shows us the name of the trigger that's about to execute.
Note: the PostgreSQL execution order diverges from the order decreed by the SQL standard. That states that the triggers should execute in the order they were defined.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Hello Hans and thx for your article
What do you think is the best use of triggers? In my experience, triggers are often used to perform application actions (for example if update here, do calculations and update data in an another table). I think it's best to leave the application code... in the code and not in the database.
I wouldn't put application code in triggers. But triggers are indispensable for everything that has to do with the integrity and consistency of the database.
Your example is a case in point: to maintain redundant data in the database reliably, you need a trigger. Also, to preprocess data that gets inserted (converting to lower case etc.), a trigger is the way to go.
Yes, you can do all these things from the application, but that's more complicated and error-prone than doing it with a trigger (you could forget a code path). But with a solution in the application, any manual intervention in the database will likely break consistency. With a trigger, there is a guarantee that this cannot happen.
My soft rule: if the code is too long and too complicated, it shouldn't be a trigger. If it is about simple rules that concern consistency in the database, it is probably a trigger.