Gyaan

Stored Procedures & Triggers

intermediate sql stored-procedures triggers plpgsql orm

Stored procedures and triggers let us run logic inside the database rather than in our application code. They’ve been around for decades, and while modern apps don’t use them as heavily as before, they’re still important to understand.

Stored Procedures

A stored procedure is a block of SQL code that we save in the database and call by name. Think of it like a function — it takes parameters, does some work, and optionally returns results.

Why Use Them?

  • Reduce network roundtrips — instead of sending 10 queries from our app, send one procedure call
  • Enforce business logic at the database level — the logic runs no matter which app connects
  • Security — grant users permission to execute a procedure without giving them direct table access
  • Reusability — write once, call from anywhere

Basic Syntax (PostgreSQL)

-- Create a stored procedure to transfer funds
CREATE OR REPLACE PROCEDURE transfer_funds(
    sender_id INT,
    receiver_id INT,
    amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Debit the sender
    UPDATE accounts SET balance = balance - amount
    WHERE id = sender_id;

    -- Check if sender had enough balance
    IF NOT FOUND OR (SELECT balance FROM accounts WHERE id = sender_id) < 0 THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

    -- Credit the receiver
    UPDATE accounts SET balance = balance + amount
    WHERE id = receiver_id;

    -- Log the transaction
    INSERT INTO transaction_log (from_id, to_id, amount, created_at)
    VALUES (sender_id, receiver_id, amount, NOW());
END;
$$;

-- Call the procedure
CALL transfer_funds(1, 2, 500.00);

Functions vs Procedures

In PostgreSQL, there’s a distinction:

-- FUNCTION: returns a value, can be used in SELECT
CREATE OR REPLACE FUNCTION get_employee_count(dept_id INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    emp_count INT;
BEGIN
    SELECT COUNT(*) INTO emp_count
    FROM employees WHERE department_id = dept_id;
    RETURN emp_count;
END;
$$;

-- Use in a query
SELECT department_id, get_employee_count(department_id) FROM departments;

-- PROCEDURE: doesn't return a value, called with CALL
-- Can manage transactions (COMMIT/ROLLBACK inside)
CALL transfer_funds(1, 2, 500.00);

Triggers

A trigger is a stored procedure that fires automatically when a specific event happens on a table — INSERT, UPDATE, or DELETE. We don’t call it manually; it runs on its own.

BEFORE vs AFTER Triggers

  • BEFORE triggers run before the operation. We can modify the data or cancel the operation entirely.
  • AFTER triggers run after the operation. Good for logging, auditing, or cascading updates.
-- Trigger function: automatically set updated_at on every UPDATE
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at = NOW();  -- NEW refers to the row being inserted/updated
    RETURN NEW;
END;
$$;

-- Attach the trigger to the employees table
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON employees        -- fires BEFORE every UPDATE
    FOR EACH ROW                      -- runs once per row
    EXECUTE FUNCTION update_timestamp();

-- Now any UPDATE on employees automatically sets updated_at
UPDATE employees SET salary = 90000 WHERE id = 1;
-- updated_at is automatically set to NOW() — we didn't have to include it

INSERT, UPDATE, DELETE Triggers

-- Audit log: track who deleted what
CREATE OR REPLACE FUNCTION log_deletion()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, old_data, deleted_at)
    VALUES (
        TG_TABLE_NAME,           -- name of the table that fired the trigger
        OLD.id,                  -- OLD refers to the row being deleted/updated
        'DELETE',
        row_to_json(OLD),        -- save the entire deleted row as JSON
        NOW()
    );
    RETURN OLD;
END;
$$;

-- Fire AFTER delete on any important table
CREATE TRIGGER audit_employee_delete
    AFTER DELETE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION log_deletion();

Conditional Triggers

We can add a WHEN clause to only fire the trigger under certain conditions:

-- Only fire when salary actually changes (not on every update)
CREATE TRIGGER salary_change_alert
    AFTER UPDATE ON employees
    FOR EACH ROW
    WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
    EXECUTE FUNCTION notify_salary_change();

Common Trigger Use Cases

  • Auto-timestamps — set created_at on INSERT, updated_at on UPDATE
  • Audit logging — track all changes to sensitive tables
  • Validation — reject invalid data before it’s written
  • Cascading updates — update a denormalized counter when related data changes
  • Notifications — send a NOTIFY event when something changes (PostgreSQL)

The Downsides

Stored procedures and triggers have fallen out of favor in modern application development. Here’s why:

  1. Hidden logic — business rules live in two places (app + database). Debugging is harder.
  2. Hard to test — unit testing a trigger is much harder than testing application code.
  3. Version control — database code is harder to track in git compared to app code.
  4. Portability — PL/pgSQL doesn’t work in MySQL. T-SQL doesn’t work in PostgreSQL. Stored procedure languages are vendor-specific.
  5. Scaling — putting logic in the database means the database does more work. It’s harder to scale a database than stateless app servers.
  6. ORM friction — ORMs (Prisma, SQLAlchemy, Sequelize) manage schema and queries from the application layer, making stored procedures redundant for most use cases.

Why Modern Apps Use ORMs Instead

ORMs like Prisma, SQLAlchemy, and Sequelize handle most of what stored procedures used to do — all from application code:

-- Instead of a stored procedure for creating an order,
-- the application code handles it:
--
-- async function createOrder(userId, items) {
--   const tx = await prisma.$transaction([
--     prisma.order.create({ data: { userId, total } }),
--     prisma.inventory.updateMany({ ... }),
--     prisma.auditLog.create({ data: { ... } }),
--   ]);
--   return tx;
-- }
--
-- Benefits: testable, in git, uses the same language as the rest of the app

When Stored Procedures/Triggers Still Make Sense

Despite the trend away from them, there are valid use cases:

  • Multi-application databases — when multiple apps share one database, enforcing rules at the database level ensures consistency
  • Performance-critical operations — eliminating network roundtrips matters for bulk operations
  • Audit requirements — regulatory compliance may require database-level audit triggers that can’t be bypassed by application code
  • Auto-timestamps — even ORM-heavy apps often use triggers for updated_at because it catches manual SQL updates too

Interview Tip

Interviewers often ask “What are triggers? Give an example.” The updated_at auto-timestamp trigger is the perfect example — it’s simple, practical, and universally understood. If they ask about pros and cons, mentioning the testability and portability issues shows we’ve thought about this in a real-world context, not just theoretically.

References