AFTER trigger behavior in PostgreSQL

Introduction

An AFTER trigger runs after a database action like insert, update, or delete finishes. It helps you do extra work once the main change is done.

You want to log changes after a row is added to a table.

You need to update related tables only after a record is successfully updated.

You want to send notifications after data is deleted.

You want to keep audit trails after data changes.

You want to enforce complex business rules after the main action.

Syntax

PostgreSQL

CREATE TRIGGER trigger_name
AFTER INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
EXECUTE FUNCTION function_name();

AFTER triggers run after the main action completes successfully.

You can define AFTER triggers for INSERT, UPDATE, or DELETE actions.

Examples

This trigger runs after a new employee is added to the employees table.

PostgreSQL

CREATE TRIGGER log_after_insert
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION log_employee_insert();

This trigger runs after an order record is updated.

PostgreSQL

CREATE TRIGGER audit_after_update
AFTER UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION audit_order_update();

This trigger runs after a product is deleted.

PostgreSQL

CREATE TRIGGER notify_after_delete
AFTER DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION notify_product_delete();

Sample Program

This example creates two tables: employees and employee_logs. It defines an AFTER INSERT trigger on employees that adds a log entry after a new employee is added. Then it inserts one employee and shows the log.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employee_logs (
  log_id SERIAL PRIMARY KEY,
  employee_id INT,
  action TEXT,
  log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_employee_insert() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO employee_logs(employee_id, action) VALUES (NEW.id, 'INSERT');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION log_employee_insert();

INSERT INTO employees(name) VALUES ('Alice');

SELECT * FROM employee_logs;

Important Notes

AFTER triggers cannot modify the row being inserted or updated because the main action is already done.

If the AFTER trigger fails, the whole transaction rolls back, so use them carefully.

Summary

AFTER triggers run after the main database action completes successfully.

They are useful for logging, notifications, and related updates.

You define them with CREATE TRIGGER and link to a function that runs after insert, update, or delete.