Row-level vs statement-level triggers in PostgreSQL - Hands-On Comparison

Row-level vs Statement-level Triggers in PostgreSQL

📖 Scenario: You are managing a PostgreSQL database for a small online store. You want to track changes to the orders table. Specifically, you want to log each individual order change and also keep a count of how many changes happen in a batch.

🎯 Goal: Build two triggers on the orders table: one row-level trigger that logs each changed order, and one statement-level trigger that counts how many changes happened in a statement.

📋 What You'll Learn

Create an orders table with columns order_id (integer primary key) and amount (numeric).

Create a log_changes table to store logs of individual order changes with columns order_id and changed_at timestamp.

Create a change_count table to store the count of changes per statement with columns count_id (serial primary key) and change_total integer.

Write a row-level trigger function that inserts a row into log_changes for each changed order.

Write a statement-level trigger function that inserts a row into change_count with the total number of changes in the statement.

Attach the row-level trigger to fire AFTER each row update on orders.

Attach the statement-level trigger to fire AFTER the entire UPDATE statement on orders.

💡 Why This Matters

🌍 Real World

Triggers help automate tasks like auditing changes or enforcing rules in databases used by businesses.

💼 Career

Understanding triggers is important for database administrators and backend developers to maintain data integrity and automate workflows.

1

Create the orders and log_changes tables

Create a table called orders with columns order_id as integer primary key and amount as numeric. Also create a table called log_changes with columns order_id as integer and changed_at as timestamp without time zone.

PostgreSQL

-- Your code here to create the tables

Need a hint?

Use CREATE TABLE statements with the exact column names and types.

2

Create the change_count table

Create a table called change_count with columns count_id as serial primary key and change_total as integer.

PostgreSQL

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    amount NUMERIC
);

CREATE TABLE log_changes (
    order_id INTEGER,
    changed_at TIMESTAMP WITHOUT TIME ZONE
);
-- Your code here to create change_count table

Need a hint?

Use SERIAL for auto-incrementing primary key.

3

Write the row-level trigger function

Write a function called log_order_change that returns trigger. This function should insert into log_changes the order_id from NEW and the current timestamp using NOW(). The function should return NEW.

PostgreSQL

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    amount NUMERIC
);

CREATE TABLE log_changes (
    order_id INTEGER,
    changed_at TIMESTAMP WITHOUT TIME ZONE
);

CREATE TABLE change_count (
    count_id SERIAL PRIMARY KEY,
    change_total INTEGER
);

-- Your code here to create the row-level trigger function

Need a hint?

Use NEW.order_id to get the updated row's order_id and NOW() for the timestamp.

4

Create triggers for row-level and statement-level events

Create a row-level trigger called after_order_update_row that fires AFTER UPDATE on orders for EACH ROW and calls log_order_change. Then create a statement-level trigger called after_order_update_stmt that fires AFTER UPDATE on orders for EACH STATEMENT and calls a new function count_order_changes. Also write the count_order_changes function that inserts the number of rows affected by the statement into change_count using GET DIAGNOSTICS to get the row count.

PostgreSQL

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    amount NUMERIC
);

CREATE TABLE log_changes (
    order_id INTEGER,
    changed_at TIMESTAMP WITHOUT TIME ZONE
);

CREATE TABLE change_count (
    count_id SERIAL PRIMARY KEY,
    change_total INTEGER
);

CREATE OR REPLACE FUNCTION log_order_change() RETURNS trigger AS $$
BEGIN
    INSERT INTO log_changes(order_id, changed_at) VALUES (NEW.order_id, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Your code here to create count_order_changes function and both triggers

Need a hint?

Use GET DIAGNOSTICS to get the number of rows affected by the statement. Attach triggers with CREATE TRIGGER specifying FOR EACH ROW or FOR EACH STATEMENT.