Stored Procedures & Triggers Cheat Sheet

Covers writing stored procedures, functions, and triggers in SQL for encapsulating business logic and automating actions on data changes.

2 PagesIntermediateMar 20, 2026

PL/pgSQL Function

A reusable function that returns a scalar value.

sql

CREATE OR REPLACE FUNCTION get_customer_total(p_customer_id INT)RETURNS NUMERIC AS $$DECLARE  v_total NUMERIC;BEGIN  SELECT COALESCE(SUM(total), 0) INTO v_total  FROM orders  WHERE customer_id = p_customer_id;  RETURN v_total;END;$$ LANGUAGE plpgsql;-- Call itSELECT get_customer_total(42);

MySQL Stored Procedure

A procedure that performs a transactional update.

sql

DELIMITER $$CREATE PROCEDURE ApplyDiscount(IN p_order_id INT, IN p_pct DECIMAL(5,2))BEGIN  DECLARE v_total DECIMAL(10,2);  START TRANSACTION;  UPDATE orders  SET total = total - (total * p_pct / 100)  WHERE id = p_order_id;  COMMIT;END $$DELIMITER ;CALL ApplyDiscount(101, 10.00);

Trigger for Audit Logging

Automatically log status changes on update.

sql

CREATE TABLE order_audit (  id SERIAL PRIMARY KEY,  order_id INT,  old_status TEXT,  new_status TEXT,  changed_at TIMESTAMP DEFAULT now());CREATE OR REPLACE FUNCTION log_status_change() RETURNS TRIGGER AS $$BEGIN  IF OLD.status IS DISTINCT FROM NEW.status THEN    INSERT INTO order_audit(order_id, old_status, new_status)    VALUES (OLD.id, OLD.status, NEW.status);  END IF;  RETURN NEW;END;$$ LANGUAGE plpgsql;CREATE TRIGGER trg_order_status_changeAFTER UPDATE ON ordersFOR EACH ROWEXECUTE FUNCTION log_status_change();

Key Concepts

Terminology for server-side database logic.

Stored procedure- A named, precompiled block of SQL/procedural code stored in the database and invoked with CALL
Function- Similar to a procedure but returns a value and can be used inside a SELECT statement
Trigger- Code that automatically runs BEFORE or AFTER an INSERT/UPDATE/DELETE on a table
ROW vs STATEMENT trigger- FOR EACH ROW fires once per affected row; a statement-level trigger fires once per statement regardless of rows affected
OLD / NEW- Pseudo-records available inside a trigger representing the row before (OLD) and after (NEW) the change
Recursive trigger risk- A trigger that modifies the same table it's attached to can cause infinite recursion if not guarded

Pro Tip

Keep triggers side-effect-light and fast — logic buried in triggers is invisible to anyone reading application code and can silently slow down bulk inserts/updates; prefer explicit application-layer logic or a documented CDC pipeline for anything beyond simple auditing or constraint enforcement.

Was this cheat sheet helpful?

Database Design & ER Diagrams Cheat Sheet

Database Backup & Recovery Cheat Sheet

Explore Topics

#StoredProceduresTriggers#StoredProceduresTriggersCheatSheet#Database#Intermediate#PLPgSQLFunction#MySQLStoredProcedure#TriggerForAuditLogging#KeyConcepts#Functions#Databases#CheatSheet#SkillVeris

Sri Hayavadhana Info-Tech

Professional Web Designing Services