Database Triggers in PostgreSQL: A Deep Dive using AWS RDS

Photo by Jr Korpa on Unsplash

Database triggers are special types of objects. They look like database procedures and functions but they are only executed in response to a certain event. The procedural code in the body is "triggered" by the specified DML or DDL operations on the database. The permitted operations are INSERT, UPDATE, DELETE (DML) and TRUNCATE (DDL) operations and the execution of the procedural code attached to the trigger could be BEFORE, AFTER or INSTEAD OF the operations, but exactly once per SQL statement or per modified row.

This blog post uses Postgres and PGSQL as the underlying technology

Therefore, there are multiple ways to classify triggers. These are

1. Based on WHEN you want the trigger function to be executed: These are BEFORE, AFTER or INSTEAD OF triggers.
2. Per-row or Per-statement triggers: The keywords that represent per-row triggers are FOR EACH ROW while the per-statement keyword is FOR EACH STATEMENT.

Note: FOR EACH STATEMENT is the default, if it is not declared.

A combination of both classes is required when building the trigger. That is, the trigger must have the WHEN clause (before, after or instead of) and must also either be per row or per statement.

The comprehensive documentation on how to combine both classifications can be found here.

Before we dive into the syntax and how to create a trigger in Postgres, let's consider the use cases for database triggers, its advantages and disadvantages

Use cases for database triggers

1. Auditing changes on DML events
2. Enforcing data and referential integrity that cannot be easily defined using constraints.
3. Enhancing security. For instance, preventing DML operations on a table after regular business hours
4. Gathering Statistics

Advantages

1. Triggers are efficient when used appropriately. For instance, to carry out an automated action, eliminating the need for manual intervention.
2. The functions attached to the trigger can be called in other code or attached to other objects. Therefore, potentially saving development time. A good example would be a trigger function to log all insertions on a table. This sort of function could be table-agnostic and highly reusable.
3. Triggers can offer a high level of control.
4. Just like other database objects, triggers can improve overall performance by moving workloads from the application layer to the database layer.

Disadvantages

1. Triggers can be complex to write and troubleshoot.
2. There is a performance overhead by introducing an additional workload when triggered by the DML operation.
3. They are programmatic and easy to alter or disable. Therefore, they can not be fully relied on as security mechanisms and must be used with caution in this case.
4. When used as constraints, they are more error-prone because they have to be developed.

Syntax

In Postgres, a trigger is made up of 2 separately defined parts. The function/procedure (basically the block of code that will be executed) and the trigger definition itself. We will get into the syntax shortly using code snippets, but before that, I will describe both the trigger function and the trigger specification.

The Trigger Function

The function must be defined before the trigger definition. it does not differ from the typical database function. However, the return type in the function specification must be TRIGGER. Below is an abridged version of a function specification

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype ]
  { LANGUAGE lang_name
    | sql_body
  }

Where the rettype is replaced with the keyword TRIGGER

The Trigger Specification

To define the trigger, we use the CREATE TRIGGER keywords alongside other options. It is also here that the function created earlier is executed.

Trigger Syntax

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE function_name ( arguments )

where the event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

Example: Audit use case

Let's create a trigger for one of my favourite use cases for triggers, which is auditing operations on a certain table.
Here, we will act as data professionals, setting up a trigger to audit changes to employee details

First, we create 2 tables, one to house employee details employee and another to capture relevant audit details employee_audit. All tables will be under the hr schema.

-- Create demo tables
CREATE table IF NOT EXISTS hr.employees  
( 
    employee_id    INTEGER CONSTRAINT emp_emp_id_pk PRIMARY KEY, 
    first_name     VARCHAR(20),
    last_name      VARCHAR(25)  CONSTRAINT emp_last_name_nn NOT NULL, 
    email          VARCHAR(25)  CONSTRAINT emp_email_nn NOT NULL, CONSTRAINT emp_email_uk UNIQUE (email),  
    phone_number   VARCHAR(20),
    hire_date      DATE  CONSTRAINT emp_hire_date_nn NOT NULL,
    job_id         VARCHAR(10)  CONSTRAINT emp_job_nn NOT NULL,  
    salary         NUMERIC(8,2)  CONSTRAINT emp_salary_min CHECK (salary > 0),
    commission_pct NUMERIC(4,2),
    manager_id     INTEGER CONSTRAINT emp_manager_fk REFERENCES employees(employee_id),
    department_id  INTEGER
);



CREATE table IF NOT EXISTS hr.employees_audit
( 
    employee_id    integer,
    first_name     VARCHAR(20),  
    last_name      VARCHAR(25),  
    email          VARCHAR(25),
    phone_number   VARCHAR(20),  
    hire_date      DATE,
    job_id         VARCHAR(10),
    salary         NUMERIC(8,2),  
    commission_pct NUMERIC(4,2),  
    manager_id     INTEGER,  
    department_id  integer, 
    date_changed   DATE constraint emp_aud_date_change not null,
    client_ip    VARCHAR(25),
    client_host_name VARCHAR(25),
    client_db_username VARCHAR(30),
    client_application varchar(80)
);

Remember! To create a trigger in Postgres, we must create the function that is going to be executed by the trigger. If you need a refresher on writing database functions, please visit the functions post in this series. Here we go!

CREATE OR REPLACE FUNCTION hr.log_employee_changes_function()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
declare
    date_of_change date := current_date;
    client_ip varchar(25);
    hostname varchar(25);
    client_db_user varchar(25);
    client_application varchar(80);
begin
    IF new <> old THEN 

        select pg_catalog.inet_client_addr() 
        into client_ip;

        select client_hostname
        into hostname
        from pg_catalog.pg_stat_activity
        where pid = pg_backend_pid();

        select usename
        into client_db_user
        from pg_catalog.pg_stat_activity
        where pid = pg_backend_pid();

        select application_name
        into client_application
        from pg_catalog.pg_stat_activity
        where pid = pg_backend_pid();

        insert
            into
            hr.employees_audit
        values(
            old.employee_id, 
            old.first_name,
            old.last_name,
            old.email,
            old.phone_number,
            old.hire_date,
            old.job_id,
            old.salary,
            old.commission_pct, 
            old.manager_id,
            old.department_id,
            date_of_change,
            client_ip,
            hostname,
            client_db_user,
            client_application
        ) ;

    END IF;

    RETURN NEW;
END;
$$

The above function returns a TRIGGER. In its body, it compares the new and old values to see if there is a change. If there is, it collects some additional information specific to that session such as the database user, IP address of the client, as well as the client application name and computes the date of that operation using the CURRENT_DATE built-in function.

All these are inserted into the employee_audit table. But when is it inserted? Is it before or after a change to the employee data? The only way we can find out is when we define our trigger using the CREATE TRIGGER keywords.

CREATE OR REPLACE TRIGGER log_employee_changes_trigger 
  BEFORE UPDATE
  ON hr.employees
  FOR EACH ROW
  EXECUTE PROCEDURE hr.log_employee_changes_function();

One thing to notice, the trigger name does not carry the HR schema as a prefix when being defined. This is because the trigger inherits the schema of the table and this is specified in the Postgres doc here.

TLDR
Triggers are special objects in a database that are executed when certain events happen. There are several cases when a database trigger could be useful such as enforcing constraints, tracking changes on tables that closely relate to business processes, and more.
Although useful, they have their pros and cons. Some advantages are that they provide reusable code, and can be highly efficient, while on the flip side, they cannot be relied on as a security mechanism and may be complex to troubleshoot.

Overall, we have created a fairly simple database trigger and we can be proud of ourselves for understanding the syntax. Please leave a comment on what you think database triggers can be used for in your organization, project etc.