Using Trigger

Introduction

A trigger is a special structure that automatically executes a function (called a Trigger Function) when a specific event occurs on a table or a view, meaning that when someone performs actions such as INSERT, UPDATE or DELETE, the Trigger will immediately be activated to perform a pre-programmed task.

Use cases

Triggers are extremely useful when you want to automate data management tasks without writing additional code on the application side, these are the most common uses:

Data Validation
- Although Postgres has built-in constraints like CHECK, NOT NULL or FOREIGN KEY, sometimes you need more complex rules.
- Example: Disallowing updates to the date_of_birth column if the calculated age is less than 18, or not allowing employees to schedule appointments on Sundays.
Data Manipulation
- Triggers can be used to automatically change data before it is saved into storage.
- Example: Automatically updating the updated_at column to the current time whenever someone edits a row of data.
Auditing / Logging
- When you need to track who modified what and when they modified it for security or auditing purposes.
- Example: When a customer is deleted from the customer table, the Trigger will automatically copy all information of that customer along with the name of the person who deleted it and the time into another table named deleted_customer_log.
Data Synchronization
- Used when a change in data within one table requires calculating or updating corresponding data into another table.
- Example: When an order is paid (orders table), the Trigger will automatically run to deduct the corresponding item quantity in the warehouse (stock table).

Mechanics of operation

To create a Trigger, you need to follow 2 steps:

Write a Trigger Function: This is the function containing the logic processing code. This function must return a data type of TRIGGER.
Create a Trigger attached to a table: Clearly define when this Trigger will run.

You can configure the execution time of the Trigger very flexibly:

BEFORE: Runs before the action (Insert/Update/Delete) is executed. Often used to check data or modify data before saving.
AFTER: Runs after the action has finished executing. Often used to log data or update other tables.
INSTEAD OF: Commonly used for Views to completely replace the default action.

In addition, a Trigger can run FOR EACH ROW to execute for every affected row or FOR EACH STATEMENT to execute exactly once for the entire SQL statement, no matter how many rows that statement changes.

Detail

First, let's create the following tables

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    discount_price DECIMAL(10,2) NOT NULL,
    category TEXT NOT NULL,
    stock INT NOT NULL
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT REFERENCES products(id),
    quantity INT NOT NULL,
    total_price DECIMAL(10,2)
);

CREATE TABLE price_update_statements_log (
    id SERIAL PRIMARY KEY,
    executed_by VARCHAR(100),
    action_type VARCHAR(10),
    affected_rows_approx INT,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

products: used to store product information
order_items: stores order details
price_update_statements_log: logs mass price updates, usually during flash sales

Next is the Function and BEFORE INSERT Trigger

CREATE OR REPLACE FUNCTION check_stock_and_calculate_price()
RETURNS TRIGGER AS $$
DECLARE
    v_stock INT;
    v_price DECIMAL(10,2);
BEGIN
    SELECT stock, price INTO v_stock, v_price FROM products WHERE id = NEW.product_id;

    IF NEW.quantity > v_stock THEN
        RAISE EXCEPTION 'This product only has % items left in stock!', v_stock;
    END IF;

    NEW.total_price := v_price * NEW.quantity;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_before_insert_order_item
BEFORE INSERT ON order_items
FOR EACH ROW
EXECUTE FUNCTION check_stock_and_calculate_price();

This trigger will run before inserting a record into the order_items table
It will automatically check whether the quantity of products in the products table is sufficient for ordering or not
Then it calculates total_price = current price * order quantity

This is the Function and AFTER INSERT Trigger

CREATE OR REPLACE FUNCTION deduct_product_stock()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE products 
    SET stock = stock - NEW.quantity 
    WHERE id = NEW.product_id;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_after_insert_order_item
AFTER INSERT ON order_items
FOR EACH ROW
EXECUTE FUNCTION deduct_product_stock();

This trigger will run after inserting a record into the order_items table
It will deduct the corresponding order quantity in the products table

This is the Function and INSTEAD OF Trigger for a Standard View

CREATE VIEW v_product_catalog AS 
SELECT id, name, price FROM products;

CREATE OR REPLACE FUNCTION update_product_safe()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE products 
    SET name = NEW.name 
    WHERE id = OLD.id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_instead_of_update_product
INSTEAD OF UPDATE ON v_product_catalog
FOR EACH ROW
EXECUTE FUNCTION update_product_safe();

This trigger will run when updating the Standard View named v_product_catalog
In case you only want developers using this View to be able to change the name of the product, if they edit other information such as price or category it will ignore it

This is the Function and Trigger with FOR EACH STATEMENT

CREATE OR REPLACE FUNCTION log_mass_price_update()
RETURNS TRIGGER AS $$
DECLARE
    v_rows_affected INT;
BEGIN
    SELECT COUNT(*) INTO v_rows_affected FROM transition_table;

    IF v_rows_affected > 0 THEN
        INSERT INTO price_update_statements_log (executed_by, action_type, affected_rows_approx)
        VALUES (
            CURRENT_USER,
            TG_OP,
            v_rows_affected
        );
    END IF;

    RETURN NULL; 
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_statement_price_update
AFTER UPDATE ON products
REFERENCING NEW TABLE AS transition_table
FOR EACH STATEMENT
EXECUTE FUNCTION log_mass_price_update();

Used during flash sales when users want to mass change discounts for a certain group of products
At this time, a single UPDATE statement will affect many records in the products table and will write a log back into the price_update_statements_log table
Therefore, using a Trigger with FOR EACH STATEMENT, here v_rows_affected is counted from transition_table (which is a virtual table) containing rows affected when the Trigger operates

To check the results, use the following statements

-- Statement 1
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 2);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 200);

-- Statement 2
UPDATE v_product_catalog SET name = 'product name updated', price = 1.00 WHERE id = 1;

-- Statement 3
UPDATE products SET discount_price = price * 0.8 WHERE category = 'Electronics';
select * from price_update_statements_log

Statement 1: When creating an order, if the quantity is valid, the Trigger will run to deduct stock and calculate total_price
Statement 2: Performs an UPDATE on product name and price into the v_product_catalog View, but the Trigger will only update the product name
Statement 3: Executes an UPDATE to calculate discount_price, then the number of affected rows will be logged into the price_update_statements_log table