Trigger function creation in PostgreSQL

Introduction

A trigger function runs automatically when certain changes happen in a database table. It helps keep data correct or do extra work without you typing commands every time.

You want to check or change data right after someone adds or updates a row.

You need to keep track of changes by saving history automatically.

You want to prevent wrong data from being saved.

You want to update related tables when data changes.

You want to send notifications or logs when data changes.

Syntax

PostgreSQL

CREATE FUNCTION function_name()
RETURNS trigger AS $$
BEGIN
    -- your code here
    RETURN NEW; -- or RETURN OLD;
END;
$$ LANGUAGE plpgsql;

The function must return type 'trigger'.

Use RETURN NEW to keep the new row or RETURN OLD to keep the old row in UPDATE/DELETE triggers.

Examples

This function prints a message when a row is updated.

PostgreSQL

CREATE FUNCTION log_update()
RETURNS trigger AS $$
BEGIN
    RAISE NOTICE 'Row updated';
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This function stops saving rows with negative amounts.

PostgreSQL

CREATE FUNCTION prevent_negative()
RETURNS trigger AS $$
BEGIN
    IF NEW.amount < 0 THEN
        RAISE EXCEPTION 'Negative amount not allowed';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Sample Program

This example creates a table and a trigger function that stops negative balances. The last insert will cause an error.

PostgreSQL

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    balance INT
);

CREATE FUNCTION check_balance()
RETURNS trigger AS $$
BEGIN
    IF NEW.balance < 0 THEN
        RAISE EXCEPTION 'Balance cannot be negative';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER balance_check
BEFORE INSERT OR UPDATE ON accounts
FOR EACH ROW EXECUTE FUNCTION check_balance();

-- Try to insert a negative balance (this will fail)
INSERT INTO accounts (balance) VALUES (-100);

Important Notes

Trigger functions must be written in a procedural language like plpgsql.

You can use NEW to access the new row and OLD for the old row inside the function.

Remember to attach the trigger function to a table with CREATE TRIGGER.

Summary

Trigger functions run automatically on table changes.

They must return type 'trigger' and use RETURN NEW or RETURN OLD.

Use triggers to enforce rules or automate tasks in the database.