SQL

A SQL trigger is code that the database automatically runs when a specific event happens, such as an INSERT, UPDATE, or DELETE.

Triggers are useful when you want the database to automatically perform an action whenever data changes  without requiring the application to remember to do it.

And we want to keep a history of every status change:

Suppose we have an orders table:

And we want to keep a history of every status change:

UPDATE orders
SET status = 'shipped'
WHERE id = 101;

For PostgreSQL:

CREATE OR REPLACE FUNCTION record_status_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.status <> NEW.status THEN
        INSERT INTO order_history (
            order_id,
            old_status,
            new_status
        )
        VALUES (
            OLD.id,
            OLD.status,
            NEW.status
        );
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Then attach the function to the table:

CREATE TRIGGER order_status_history
AFTER UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION record_status_change();

Now this:

UPDATE orders
SET status = 'shipped'
WHERE id = 101;

automatically produces:

When are triggers useful?

Common examples include:

• Keeping audit/history records
• Maintaining related data
• Automatically recording timestamps
• Enforcing database-level rules
• Logging important changes

One important trade-off: triggers can hide logic inside the database, which can make an application harder to understand and debug. Use them when the rule genuinely belongs at the database level.

Window functions allow you to perform calculations across a set of related rows without collapsing those rows into a single result. They are especially useful for rankings, running totals, and comparing a row with previous or next rows.

The key difference from GROUP BY is that GROUP BY combines rows, while a window function keeps the original rows.

Suppose we have:

We want to find each employee's salary rank within their department:

SELECT
    name,
    department,
    salary,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS department_rank
FROM employees;

The result is:

We could add a regular ORDER BY to make the result easier to read:

SELECT
    name,
    department,
    salary,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS department_rank
FROM employees
ORDER BY department, department_rank;

Why not just use ORDER BY?

A regular: 

ORDER BY salary DESC

would simply sort all employees by salary.

It would not calculate a rank for each employee, and it would not independently rank employees within each department.

Simple mental model

Partition → separate the groups
Order → determine the ranking order
Rank → assign a position to each row
Result → keep all original rows