# Elevating Database Functionality with Triggers 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 ```sql 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 ```sql 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: ```sql 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. ```sql -- 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! ```sql 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. ```sql 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.