PostgreSQL Triggers: Creation, Deletion, Examples

Technical writer

PostgreSQL

11.04.2025

Reading time: 6 min

Triggers are used to instruct the PostgreSQL engine to execute a piece of code when a specific event occurs. They act like a catalyst for changes — a trigger that initiates a chain of events.

A trigger must be associated with a specific table, view (pseudo-table), or foreign table. It activates only when operations are performed on that entity — INSERT, UPDATE, DELETE, or TRUNCATE. Depending on your needs, the trigger can run before, after, or instead of the event/operation.

Types of Triggers

PotgreSQL triggers are divided into two types depending on the level at which they operate:

FOR EACH ROW: The function is called for each individual row affected by the event. For example, an UPDATE affecting 100 rows will trigger the function 100 times — once for each row.
FOR EACH STATEMENT: The function is called just once per SQL statement, regardless of how many rows are affected.

Triggers Use Cases

Triggers are powerful tools with many use cases. Some examples include:

Tracking Changes: You can use triggers to log transaction details when changes occur in a table.
Data Validation: Triggers can enforce constraints before applying changes to the database.
Auto-Filling Fields: Automatically populate fields based on new transaction data.
Performance Optimization: For example, on a server like Hostman, you might log timestamps in a table and want to aggregate that data every 6 hours (four intervals a day). Scanning and aggregating all rows repeatedly is inefficient on large datasets.

Instead of recalculating everything each time, you can use Materialized Views, which cache results. However, these are recalculated entirely on each refresh, which is still problematic on large datasets.

Triggers can solve this by acting like "smart" materialized views: updating only the affected row, not the entire dataset.

Creating a Trigger

Let’s look at how to create a trigger in PostgreSQL.

Syntax:

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

Where the event can be one or more of the following:

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

Key notes:

You can create (CREATE) or replace (REPLACE) an existing trigger.
You associate the function directly with a table/view/foreign table. The code runs only when the specified event occurs on that entity.
Triggers using INSTEAD OF must be marked with FOR EACH ROW and are only allowed on views.
Triggers using BEFORE or AFTER on views must be FOR EACH STATEMENT.

Refer to PostgreSQL documentation for a summary table that outlines these rules.

Basic Examples of PostgreSQL Triggers

Trigger before update:

CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION check_account_update();

Trigger before update of a specific column:

CREATE OR REPLACE TRIGGER check_update
    BEFORE UPDATE OF balance ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION check_account_update();

Trigger after update to log changes:

CREATE TRIGGER log_update
    AFTER UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE FUNCTION log_account_update();

INSTEAD OF trigger for view insertions:

CREATE TRIGGER view_insert
    INSTEAD OF INSERT ON my_view
    FOR EACH ROW
    EXECUTE FUNCTION view_insert_row();

Trigger on delete:

CREATE TRIGGER example_delete_trigger
    AFTER DELETE ON my_view
    FOR EACH ROW
    EXECUTE PROCEDURE aft_delete();

Practical Example: Inserting Into Two Tables

Let’s create a PostgreSQL trigger that adds information about a new employee to a second table when data is inserted into the primary table.

Step 1: Create both tables:

CREATE TABLE "Employee" (
  "EmployeeId" INT NOT NULL,
  "LastName" VARCHAR(20) NOT NULL,
  "FirstName" VARCHAR(20) NOT NULL,
  "Title" VARCHAR(30),
  "ReportsTo" INT,
  "BirthDate" TIMESTAMP,
  "HireDate" TIMESTAMP,
  "Address" VARCHAR(70),
  "City" VARCHAR(40),
  "State" VARCHAR(40),
  "Country" VARCHAR(40),
  "PostalCode" VARCHAR(10),
  "Phone" VARCHAR(24),
  "Fax" VARCHAR(24),
  "Email" VARCHAR(60),
  CONSTRAINT "PK_Employee" PRIMARY KEY ("EmployeeId")
);

CREATE TABLE "Employee_Audit" (
  "EmployeeId" INT NOT NULL,
  "LastName" VARCHAR(20) NOT NULL,
  "FirstName" VARCHAR(20) NOT NULL,
  "UserName" VARCHAR(20) NOT NULL,
  "EmpAdditionTime" VARCHAR(20) NOT NULL
);

Step 2: Create the trigger function:

CREATE OR REPLACE FUNCTION employee_insert_trigger_fnc()
  RETURNS trigger AS
$$
BEGIN
  INSERT INTO "Employee_Audit" ("EmployeeId", "LastName", "FirstName", "UserName", "EmpAdditionTime")
  VALUES (NEW."EmployeeId", NEW."LastName", NEW."FirstName", current_user, current_date);
  RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

Step 3: Create the trigger:

CREATE TRIGGER employee_insert_trigger
  AFTER INSERT
  ON "Employee"
  FOR EACH ROW
  EXECUTE PROCEDURE employee_insert_trigger_fnc();

Test:

Insert a record:

INSERT INTO "Employee" 
VALUES(12, 'Smith', 'Jeff', 'Editor', 1, '1992-05-28 00:00:00', '2022-01-15 00:00:00',
'Paseo de Gracia', 'Barcelona', 'Catalonia', 'Spain', '128 665', '+15 52-469-2573',
'+15 52-469-2573', 'mail@mail.com');

Check that the data is there:

SELECT * FROM "Employee" WHERE "EmployeeId" = 12;

Check the audit log:

SELECT * FROM "Employee_Audit";

You should see something like:

EmployeeId       | 12
LastName         | Smith
FirstName        | Jeff
UserName         | postgres
EmpAdditionTime  | 2025-04-10

Perfect! It works as expected.

Modifying a Trigger

To change a trigger's properties, use CREATE OR REPLACE TRIGGER and specify the same trigger function and table. You can update the remaining properties as needed.

To rename a trigger:

ALTER TRIGGER name ON table_name RENAME TO new_name;

Check documentation for more details.

Deleting a Trigger

To delete a trigger:

DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ];

Example:

DROP TRIGGER some_example_of_trigger ON "Example";

Note:

You must own the table to delete its trigger.
Use IF EXISTS to avoid errors if the trigger doesn’t exist.
Use CASCADE to remove all dependent objects.
Use RESTRICT to prevent deletion if dependencies exist (default).

Check the documentation for more details.

Important Notes

You must have TRIGGER privilege on the table and EXECUTE privilege on the trigger function.
You can check the system catalog pg_trigger to view existing triggers.
If you define multiple triggers for the same table/event, they execute alphabetically by name.

PostgreSQL

11.04.2025

Reading time: 6 min

Similar

PostgreSQL

PostgreSQL Triggers: Creation, Deletion, Examples

Types of Triggers

Triggers Use Cases

Creating a Trigger

Basic Examples of PostgreSQL Triggers

Trigger before update:

Trigger before update of a specific column:

Trigger after update to log changes:

INSTEAD OF trigger for view insertions:

Trigger on delete:

Practical Example: Inserting Into Two Tables

Modifying a Trigger

Deleting a Trigger

Important Notes

Similar

How to Install PostgreSQL on Ubuntu

How to Deploy PostgreSQL on Kubernetes

How to Set Up Physical Streaming Replication with PostgreSQL on Ubuntu

Do you have questions, comments, or concerns?

Do you have questions,
comments, or concerns?