Quick Start

Postgres triggers provide the ability to specify database actions that should occur when operations happen in the database (INSERT, UPDATE, DELETE, TRUNCATE) on certain conditions of the affected rows.

The pgtrigger.Trigger object is the base class for all triggers. Attributes of this class mirror the syntax required for making a Postgres trigger, and one has the ability to input the exact PL/pgSQL code that is executed by Postgres in the trigger. pgtrigger also has several helper classes, like pgtrigger.Protect, that implement some core triggers you can configure without having to write PL/pgSQL syntax.

When declaring a trigger, one must provide the following attributes:

  • when

    When the trigger should happen. Can be one of pgtrigger.Before or pgtrigger.After to execute the trigger before or after an operation.

  • operation

    The operation which triggers execution of the trigger function. This can be one of pgtrigger.Update, pgtrigger.Insert, pgtrigger.Delete, pgtrigger.Truncate, or pgtrigger.UpdateOf. All of these can be OR ed together (e.g. pgtrigger.Insert | pgtrigger.Update) to configure triggering on a combination of operations.

    Note

    pgtrigger.UpdateOf is triggered when columns appear in an UPDATE statement. It will not be triggered if other triggers edit columns. See the notes in the Postgres docs for more information about this use case.

    Note

    Some conditions cannot be combined together for a valid trigger. For example, pgtrigger.UpdateOf cannot be combined with other operations.

  • condition

    An optional condition on which the trigger is executed based on the OLD and NEW row variables that are part of the trigger.

    One can use the pgtrigger.Condition class to write a free-form clause (e.g. OLD.value = "value"). The pgtrigger.Q condition also mimics Django’s Q object to specify a filter clause on the affected rows. For example, a condition of pgtrigger.Q(old__value='hello') will only trigger when the old row’s value field is hello.

Installing triggers for models

Similar to the Django admin, pgtrigger triggers are registered to models using the pgtrigger.register decorator. The decorator takes a variable amount of pgtrigger.Trigger objects that should be installed for the model.

For example, this trigger definition protects this model from being deleted:

from django.db import models
import pgtrigger


@pgtrigger.register(
    pgtrigger.Protect(operation=pgtrigger.Delete)
)
class CannotDelete(models.Model):
    field = models.CharField(max_length=16)

The triggers are installed automatically when running manage.py migrate. If a trigger definition is removed from the project, the triggers will be removed in the database. If the trigger changes, the new one will be created and the old one will be dropped during migrations.

To turn off creating triggers in migrations, configure the PGTRIGGER_INSTALL_ON_MIGRATE setting to False. Triggers can manually be configured with the following code:

Note

If triggers are disabled (as opposed to uninstalled), they have to be re-enabled again and will not be re-enabled automatically during migrations.

Trigger cookbook

Here are a few more examples of how you can configure triggers using the utilities in pgtrigger.

Keeping a field in-sync with another

We can register a pgtrigger.Trigger before an update or insert to ensure that two fields remain in sync.

import pgtrigger


@pgtrigger.register(
    pgtrigger.Trigger(
        operation=pgtrigger.Update | pgtrigger.Insert,
        when=pgtrigger.Before,
        func='NEW.in_sync_int = NEW.int_field; RETURN NEW;',
    )
)
class MyModel(models.Model):
    int_field = models.IntField()
    in_sync_int = models.IntField(help_text='Stays the same as "int_field"')

Note

When writing a “BEFORE” trigger, be sure to return the row over which the operation should be applied. Returning no row will prevent the operation from happening.

Soft-delete models

A soft-delete model is one that sets a field on the model to False instead of deleting the model from the database. For example, it is common is set an is_active field on a model to False to soft delete it.

The pgtrigger.SoftDelete trigger takes the field as an argument and sets it to False whenever a deletion happens on the model. For example:

import pgtrigger


@pgtrigger.register(pgtrigger.SoftDelete(field='is_active'))
class SoftDeleteModel(models.Model):
    # This field is set to false when the model is deleted
    is_active = models.BooleanField(default=True)

m = SoftDeleteModel.objects.create()
m.delete()

# The model will still exist, but it is no longer active
assert not SoftDeleteModel.objects.get().is_active

The pgtrigger.SoftDelete trigger allows one to do soft deletes at the database level with no instrumentation in code at the application level. This reduces the possibility for holes in the application that can accidentally delete the model when not going through the appropriate interface.

Note

When using pgtrigger.SoftDelete, keep in mind that Django will still perform cascading operations to models that reference the soft-delete model. For example, if one has a model that foreign keys to SoftDeleteModel in the example with on_delete=models.CASCADE, that model will be deleted by Django when the parent model is soft deleted. One can use models.DO_NOTHING if they wish for Django to not delete references to soft-deleted models.

Append-only models

Create an append-only model using the pgtrigger.Protect utility and registering it for the UPDATE and DELETE operations:

import pgtrigger
from django.db import models


@pgtrigger.register(
    pgtrigger.Protect(
        operation=(pgtrigger.Update | pgtrigger.Delete)
    )
)
class AppendOnlyModel(models.Model):
    my_field = models.IntField()

Note

This table can still be truncated, although this is not an operation supported by Django. One can still protect against this by adding the pgtrigger.Truncate operation.

Dynamic deletion protection

Only allow models with a deletable flag to be deleted:

import pgtrigger
from django.db import models


@pgtrigger.register(
    pgtrigger.Protect(
        operation=pgtrigger.Delete,
        condition=pgtrigger.Q(old__is_deletable=False)
    )
)
class DynamicDeletionModel(models.Model):
    is_deletable = models.BooleanField(default=False)

Redundant update protection

Want to error every time someone tries to update a row with the exact same values? Here’s how:

import pgtrigger
from django.db import models


@pgtrigger.register(
    pgtrigger.Protect(
        operation=pgtrigger.Delete,
        condition=pgtrigger.Condition(
            'OLD.* IS NOT DISTINCT FROM NEW.*'
        )
    )
)
class RedundantUpdateModel(models.Model):
    redundant_field1 = models.BooleanField(default=False)
    redundant_field2 = models.BooleanField(default=False)

Configuring triggers on external models

Triggers can be registered for models that are part of third party apps. This can be done by manually calling the pgtrigger.register decorator:

from django.contrib.auth.models import User
import pgtrigger

# Register a protection trigger for the User model
pgtrigger.register(pgtrigger.Protect(...))(User)

Note

Be sure that triggers are registered via an app config’s ready() method so that the registration happens! More information on this here.