Basics¶
The anatomy of a trigger¶
Postgres triggers are database functions written in PL/pgSQL that execute based on events and conditions.
The pgtrigger.Trigger
object is the base class for all triggers in django-pgtrigger
.
Its attributes mirror the syntax required for
making a Postgres trigger.
Here are the most common attributes you’ll use:
name
The identifying name of trigger. Is unique for every model and must be less than 48 characters.
operation
The table operation that fires a trigger. Operations are
pgtrigger.Update
,pgtrigger.Insert
,pgtrigger.Delete
,pgtrigger.Truncate
, orpgtrigger.UpdateOf
. They can beOR
ed together (e.g.pgtrigger.Insert | pgtrigger.Update
) to configure triggers on a combination of operations.Note
pgtrigger.UpdateOf
fires when columns appear in anUPDATE
statement. It will not fire if other triggers update the columns. See the notes in the Postgres docs for more information.Note
Some conditions cannot be combined. For example,
pgtrigger.UpdateOf
cannot be combined with other operations.when
When the trigger should run in relation to the operation.
pgtrigger.Before
executes the trigger before the operation, and vice versa forpgtrigger.After
.pgtrigger.InsteadOf
is used for SQL views.Note
pgtrigger.Before
andpgtrigger.After
can be used on SQL views under some circumstances. See the Postgres docs for a breakdown.condition (optional)
Conditionally execute the trigger based on the
OLD
orNEW
rows.pgtrigger.Condition
objects acceptpgtrigger.Q
andpgtrigger.F
objects for constructingWHERE
clauses with theOLD
andNEW
rows. Conditions can also be created from raw SQL. See the Trigger Cookbook for more examples.Note
Be sure to familiarize yourself with
OLD
andNEW
rows when writing conditions by consulting the Postgres docs. For example,OLD
is alwaysNULL
inpgtrigger.Insert
triggers.
Here are attributes you’ll need when writing more complex triggers.
func
The raw PL/pgSQL function that is executed.
Note
This is not the entire declared trigger function, but rather the snippet of PL/pgSQL that is nested in the
`DECLARE ... BEGIN ... END`
portion of the trigger.declare (optional)
Define additional variable declarations as a list of
(variable_name, variable_type)
tuples. For exampledeclare=[('my_var_1', 'BOOLEAN'), ('my_var_2', 'JSONB')]
.level (optional, default=pgtrigger.Row)
Configures the trigger to fire once for every row (
pgtrigger.Row
) or once for every statement (pgtrigger.Statement
).referencing (optional)
References the
OLD
andNEW
rows as transition tables in statement-level triggers. For example,pgtrigger.Referencing(old='old_table_name', new='new_table_name')
will make anold_table_name
andnew_table_name
table available as transition tables. See this StackExchange answer for additional details, and see the Trigger Cookbook for an example.Note
The
REFERENCING
construct for statement-level triggers is only available in Postgres 10 and up.timing (optional)
Create a deferrable
CONSTRAINT
trigger when set. Usepgtrigger.Immediate
to execute the trigger at the end of a statement andpgtrigger.Deferred
to execute it at the end of a transaction.Note
Deferrable triggers must have the
level
set topgtrigger.Row
andwhen
set topgtrigger.After
.
Defining and installing triggers¶
Triggers are defined in the triggers
attribute of the model Meta
class. For example, this trigger protects the model from being
deleted:
from django.db import models
import pgtrigger
class CannotDelete(models.Model):
class Meta:
triggers = [
pgtrigger.Protect(name='protect_deletes', operation=pgtrigger.Delete)
]
Triggers are installed by first running python manage.py makemigrations
and then python manage.py migrate
.
If you’d like to install a trigger on a model of a third-party app, see the Advanced Installation section. This section also covers how you can manually install, enable, and disable triggers globally.
The advantages over signals and model methods¶
There are three key advantages to using triggers over implementing the logic in a Django signal handler or by overriding model methods:
Reliability: Unlike Python code, triggers run alongside queries in the database, ensuring that nothing falls through the cracks. On the other hand, signals and model methods can provide a false sense of security. For example, signals aren’t fired for
bulk_create
, and custom model methods aren’t called in data migrations by default. Third party apps that bypass the ORM will also not work reliably.Complexity: Complexity can balloon when trying to override models, managers, or querysets to accomplish the same logic a trigger can support. Even simple routines such as conditionally running code based on a changed field are difficult to implement correctly and prone to race conditions.
Performance: Triggers can perform SQL queries without needing to do expensive round trips to the database to fetch data. This can be a major performance enhancement for routines like history tracking or data denormalization.