Database Triggers in PostgreSQL: A Deep Dive using AWS RDS
Tags: database, programming, postgres, aws
Published: 2024-02-20 01:07:04

_Photo by <a href="https://unsplash.com/@jrkorpa?utm_content=creditCopyText&utm_medium=referral&utm_source=unsplash">Jr Korpa</a> on <a href="https://unsplash.com/photos/a-window-with-rain-drops-on-the-glass-E2i7Hftb_rI?utm_content=creditCopyText&utm_medium=referral&utm_source=unsplash">Unsplash</a>_
  
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](https://www.postgresql.org/docs/current/sql-createtrigger.html).

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](https://co.hashnode.dev/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](https://www.postgresql.org/docs/current/sql-createtrigger.html). 

**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.