Skip to content

Conditional Triggers

Here's a brief guide on the many ways one can create conditional row-level triggers using django-pgtrigger. We start with the high-level utilities and make our way towards lower-level ones.

Remember, row-level triggers have access to either the NEW row being inserted or updated, or the OLD row being updated or deleted. These variables are copies of the row and can be used in the conditions of the trigger. Updates triggers, for example, can conditionally execute based on both the values of the row before the update (the OLD row) and the row after the modification (the NEW row).

Note

Consult the Postgres docs for more information on these variables.

We'll first dive into update-based triggers and the utilities django-pgtrigger provides for detecting changes on models.

Field Change Conditions

The following conditions are provided out of the box for conveniently expressing field changes:

For example, let's use this model:

class MyModel(models.Model):
    int_field = models.IntegerField()
    char_field = models.CharField(null=True)
    dt_field = models.DateTimeField(auto_now=True)

The following trigger will raise an exception if an update happens that doesn't change a single field.

pgtrigger.Protect(operation=pgtrigger.Update, condition=~pgtrigger.AnyChange())

This is also equivalent to doing:

pgtrigger.Protect(operation=pgtrigger.Update, condition=pgtrigger.AllDontChange())

Remember

If no arguments are provided to any of these utilities, they operate over all fields on the model.

Let's say we want to block updates if any changes happen to the int or char fields:

pgtrigger.Protect(
    operation=pgtrigger.Update,
    condition=pgtrigger.AnyChange("int_field", "char_field")
)

This is how the pgtrigger.ReadOnly trigger is implemented. Underneath the hood, the condition looks like this:

OLD.int_field IS DISTINCT FROM NEW.int_field
OR OLD.char_field IS DISTINCT FROM NEW.char_field

Note

IS DISTINCT FROM helps ensure that nullable objects are correctly compared since null never equals null.

One can also exclude fields in the condition. For example, this condition fires only if every field but the excluded ones change:

pgtrigger.AllChange(exclude=["dt_field"])

To automatically ignore auto_now and auto_now_add datetime fields, do:

# Fires on changes to any fields except auto_now and auto_now_add fields
pgtrigger.AnyChange(exclude_auto=True)

Remember

Included and excluded fields can both be supplied. Included fields are used as the initial fields before exclude and exclude_auto remove fields.

Targetting old and new fields with pgtrigger.Q and pgtrigger.F

We previously covered various change condition utilties. These only operate over update-based triggers. One can create fine-grained trigger conditions for all operations by using pgtrigger.Q and pgtrigger.F constructs.

For example, let's use our model from above again:

class MyModel(models.Model):
    int_field = models.IntegerField()
    char_field = models.CharField(null=True)
    dt_field = models.DateTimeField(auto_now=True)

The following condition will fire whenever the old row has an int_field greater than zero:

pgtrigger.Q(old__int_field__gt=0)

Similar to Django's syntax, the pgtrigger.Q object can reference the old__ and new__ row. The pgtrigger.F object can also be used for doing comparisons. For example, here we only fire when the int_field of the old row is greater than the int field of the new row.

pgtrigger.Q(old__int_field__gt=pgtrigger.F("new__int_field"))

Remember to use the __df operator for DISTINCT FROM and __ndf for NOT DISTINCT FROM. This is generally the behavior one desires when checking for changes of nullable fields. For example, this condition fires only when char_field is not distinct from its old version.

pgtrigger.Q(old__char_field__ndf=pgtrigger.F("new__char_field"))

Note

The above is equivalent to doing pgtrigger.AnyDontChange("char_field")

Finally, pgtrigger.Q objects can be negated, and-ed, and or-ed just like django Q objects:

pgtrigger.Q(old__char_field__ndf=pgtrigger.F("new__char_field"))
| pgtrigger.Q(new__int_field=0)

Raw SQL conditions

The utilities above should handle the majority of use cases when expressing conditions; however, users can still express raw SQL with pgtrigger.Condition. For example, here's a condition that fires if any field changes:

pgtrigger.Condition("OLD.* IS DISTINCT FROM NEW.*")

Note

The above is equivalent to pgtrigger.AnyChange().

Conditions across multiple models

Remember, trigger conditions can only be expressed based on the rows of the current model. One can't, for example, reference a joined foreign key's value. This isn't a limitation in django-pgtrigger but rather a limitation in the database.

Custom conditional logic than spans multiple tables must happen inside the function as an if/else type of statement. See this resource for an example of what this looks like.

Currently django-pgtrigger doesn't handle this case out of the box; one must write raw SQL to express if/else logic. Reach out to the author if you have a need for this to be more easily expressed in django-pgtrigger.