All 40 lessons

Lesson 37 of 40 · Advanced SQL

SQL Functions, Stored Procedures and Triggers

By Skillancy Editorial Team

Every example runs on the ShopKart dataset, already loaded in the free SQL Compiler. Click Try it in the SQL Compiler under a query to open it there, then press Run.

Functions and stored procedures save reusable logic inside the database, and triggers run logic automatically when data changes. Syntax varies more between databases here than anywhere else in SQL. The examples use PostgreSQL; the ideas carry over to MySQL and SQL Server.

	Function	Procedure	Trigger
How it runs	inside a query: `SELECT f(x)`	`CALL p(x)`	automatically on INSERT/UPDATE/DELETE
Returns a value	yes	not required	no (it changes data)
Typical use	calculations, lookups	multi-step jobs	audit logs, keeping data in sync

User-defined functions

Syntax

CREATE FUNCTION function_name(param type, ...)
RETURNS return_type
LANGUAGE sql
AS $$
  SELECT ...;
$$;

$$ ... $$ simply quotes the function body.

1. A simple SQL function

Net amount for an order line, so the formula lives in one place:

CREATE FUNCTION net_amount(qty INT, price NUMERIC, discount NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
AS $$
  SELECT ROUND(qty * price * (1 - discount / 100), 2);
$$;

SELECT order_item_id, net_amount(quantity, unit_price, discount_pct) AS net
FROM order_items
LIMIT 3;

Try it in the SQL Compiler

Result:

order_item_id	net
1	21599.10
2	2598.00
3	8074.05

(3 rows)

2. A PL/pgSQL function with logic

LANGUAGE plpgsql adds variables and IF statements. Classify a customer by lifetime spend:

CREATE FUNCTION customer_tier(p_customer_id INT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
  total NUMERIC;
BEGIN
  SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0) INTO total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.customer_id = p_customer_id AND o.status = 'Delivered';

  IF total >= 25000 THEN
    RETURN 'Gold';
  ELSIF total >= 5000 THEN
    RETURN 'Silver';
  ELSE
    RETURN 'Bronze';
  END IF;
END;
$$;

SELECT customer_name, customer_tier(customer_id) AS tier
FROM customers
ORDER BY customer_id
LIMIT 5;

Try it in the SQL Compiler

Result:

customer_name	tier
Aarav Sharma	Gold
Priya Nair	Silver
Rohan Gupta	Gold
Sneha Reddy	Silver
Vikram Singh	Bronze

(5 rows)

3. A function that returns a table

CREATE FUNCTION orders_for_city(p_city TEXT)
RETURNS TABLE (order_id INT, customer_name VARCHAR, order_date DATE)
LANGUAGE sql
AS $$
  SELECT o.order_id, c.customer_name, o.order_date
  FROM orders o JOIN customers c ON c.customer_id = o.customer_id
  WHERE c.city = p_city
  ORDER BY o.order_date;
$$;

SELECT * FROM orders_for_city('Hyderabad');

Try it in the SQL Compiler

Result:

order_id	customer_name	order_date
1005	Sneha Reddy	2025-03-11
1013	Sneha Reddy	2025-08-02
1023	Sneha Reddy	2026-01-26

(3 rows)

Stored procedures

4. A procedure for a multi-step job

Cancel an order and put its items back in stock, in one call:

CREATE PROCEDURE cancel_order(p_order_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE orders SET status = 'Cancelled', delivery_date = NULL
  WHERE order_id = p_order_id;

  UPDATE products p
  SET stock_qty = p.stock_qty + oi.quantity
  FROM order_items oi
  WHERE oi.order_id = p_order_id AND oi.product_id = p.product_id;
END;
$$;

CALL cancel_order(1023);

SELECT o.order_id, o.status, p.product_name, p.stock_qty
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
WHERE o.order_id = 1023;

Try it in the SQL Compiler

Result:

order_id	status	product_name	stock_qty
1023	Cancelled	Steel Water Bottle 1L	304

(1 row)

Order 1023 had 4 water bottles; stock went from 300 to 304.

Triggers

A trigger calls a special function (RETURNS TRIGGER) when rows change. Inside it, NEW is the row after the change and OLD is the row before.

5. An audit log for price changes

CREATE TABLE price_history (
  product_id INT,
  old_price  NUMERIC(10,2),
  new_price  NUMERIC(10,2),
  changed_on DATE DEFAULT CURRENT_DATE
);

CREATE FUNCTION log_price_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.price <> OLD.price THEN
    INSERT INTO price_history (product_id, old_price, new_price)
    VALUES (OLD.product_id, OLD.price, NEW.price);
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_price_change
AFTER UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION log_price_change();

Try it in the SQL Compiler

Result:

Query OK, 0 row(s) affected.

Now any price change is recorded automatically:

UPDATE products SET price = 21999 WHERE product_id = 101;   -- festive sale
UPDATE products SET price = 1199  WHERE product_id = 102;
UPDATE products SET stock_qty = 260 WHERE product_id = 102; -- not a price change

SELECT product_id, old_price, new_price FROM price_history;

Result:

product_id	old_price	new_price
101	23999.00	21999.00
102	1299.00	1199.00

(2 rows)

Two rows: the stock update didn't touch the price, so the IF skipped it.

Common mistakes

Hiding business logic in triggers. Data changes "by magic" and confuses everyone debugging it. Use triggers sparingly and document them.
Slow functions in big queries. A function called once per row on millions of rows can be much slower than the same logic written inline.
Forgetting RETURN NEW in a BEFORE row trigger, which silently cancels the change.

Practice question

Write a SQL function days_to_deliver(p_order_id INT) that returns the number of days between an order's order date and delivery date, then use it for orders 1001–1003.

Show solution

CREATE FUNCTION days_to_deliver(p_order_id INT)
RETURNS INT
LANGUAGE sql
AS $$
  SELECT delivery_date - order_date FROM orders WHERE order_id = p_order_id;
$$;

SELECT order_id, days_to_deliver(order_id) AS days
FROM orders
WHERE order_id BETWEEN 1001 AND 1003;

Try it in the SQL Compiler

Result:

order_id	days
1001	3
1002	5
1003	3

(3 rows)

Key takeaways

Functions return values and can be used inside SELECT.
Procedures run multi-step jobs with CALL.
Triggers run automatically on INSERT, UPDATE or DELETE, using NEW and OLD.
Keep database logic small, tested and documented.

Frequently asked questions

What is the difference between a function and a stored procedure?

A function returns a value and can be used inside a SELECT. A procedure is run with CALL, does not have to return anything, and in PostgreSQL can commit or roll back transactions inside its body.

What is a trigger in SQL?

Code that runs automatically when a row is inserted, updated or deleted in a table, for example to write an audit log or keep a stock count in sync.

Do data analysts need stored procedures?

Day to day, rarely. You mainly read and write SELECT queries. But they appear in interviews and in many companies' reporting pipelines, so knowing how to read them is useful.

← PreviousSQL Indexes and Query Optimization Next →Database Normalization (1NF, 2NF, 3NF)

Newsletter

Keep learning SQL

One email a week with SQL and analytics guides, new practice questions and cohort dates. No spam, unsubscribe anytime.

Prefer a one-page summary? Download the free SQL cheat sheet (PDF)