Computer Engineering is coming soon

We're still writing and reviewing the notes for every subject, and we'll open it when every subject is ready. Meanwhile you can practise aptitude, ask Aria about any subject, or see the branches that are ready.

Stored procedures, functions and triggers

Practical, exam-focused notes on stored procedures, functions and triggers: definitions, syntax, semantics, examples and common exam mistakes for DBMS courses.

Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.

Why it matters

Stored code inside the database saves network round-trips, enforces business rules close to the data, and helps maintain integrity. Engineers use stored procedures, functions, and triggers to implement validation, auditing, calculations and automated reactions to data changes inside the DBMS.

Key ideas

  • Trigger — a special stored program that the DBMS automatically executes (fires) when a specified table-event occurs. A trigger definition contains: the timing (BEFORE or AFTER), the event (INSERT, UPDATE, DELETE), the level (ROW or STATEMENT) and the body SQL. Example: a BEFORE INSERT ON employees FOR EACH ROW trigger.

  • Parameters: IN (input), OUT (output), INOUT (both). Functions typically accept only IN parameters (no OUT/INOUT). IN parameters do not return values to the caller; some DBMS allow assigning to an IN parameter locally, but changes do not propagate. OUT parameters are set by the routine and returned to the caller.

  • Row-level trigger vs Statement-level trigger: A row-level trigger fires once per affected row and has access to :OLD and :NEW (Oracle) or OLD/NEW row variables; a statement-level trigger fires once per statement regardless of number of rows changed and does not have per-row OLD/NEW variables.

  • BEFORE vs AFTER trigger: BEFORE triggers allow changing the row being inserted/updated before it is written; AFTER triggers see the final data as stored and typically perform actions that depend on the final state (e.g., logging). Some DBMS also support INSTEAD OF triggers (commonly on views) to handle DML on a view by redirecting it to underlying tables.

  • Mutating table problem (Oracle term): When a row-level trigger tries to query or modify the table that is currently being modified by the statement, the DBMS may throw an error (Oracle says table is mutating). The assumption behind this problem: row-by-row changes are not yet committed to a stable state for safe querying. Workarounds: use statement-level triggers, use temporary collections, or defer checks to constraints or stored procedures.

  • Privileges and security: Procedures and triggers run with privileges of the definer or invoker based on DBMS configuration (e.g., Oracle has AUTHID DEFINER/CURRENT_USER behaviour). MySQL uses SQL SECURITY DEFINER/INVOKER for routines; triggers execute with the privileges of the trigger definer. This affects access control and must be handled carefully.

  • Transaction boundaries and implicit commits: Some DDL or certain statements inside procedures cause implicit commits in some DBMS. Triggers run inside the same transaction as the triggering statement; they must be short and avoid long-running operations.

Diagram to draw (for exams): draw two parallel columns labelled "Statement-level" and "Row-level". Under each, draw a box for a multi-row UPDATE. For row-level column, draw repeated small boxes for each row labeled "trigger fires per row: NEW/OLD available". For statement-level column, draw one box for whole statement labeled "trigger fires once: no NEW/OLD". Also draw a timeline showing BEFORE (left) -> actual write -> AFTER (right), and label where BEFORE row-level and AFTER statement-level triggers run.

Formulas

(Here we list syntax lines to remember; each line is a SQL statement or pattern.) CREATE PROCEDURE proc_name (IN|OUT|INOUT param1 datatype, ...) BEGIN statements END; — proc_name: procedure name, param: parameter name and type, parameter mode comes before the name; DBMSs differ on required keywords.

CALL proc_name(arg1, arg2, ...); — invoke a stored procedure.

CREATE FUNCTION func_name (param datatype, ...) RETURNS return_datatype DETERMINISTIC|NOT DETERMINISTIC BEGIN statements RETURN expr; END; — RETURNS gives return type; DETERMINISTIC documents determinism (DBMS may require or allow this).

SELECT func_name(arg); — use function in SQL expression (if DBMS permits side-effects).

CREATE TRIGGER trig_name BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name FOR EACH ROW BEGIN statements END; — FOR EACH ROW makes it row-level; omit to make statement-level (DBMS-dependent syntax). In MySQL, only row-level triggers are supported and FOR EACH ROW is required; statement-level triggers are not available.

NEW.column_name and OLD.column_name (or :NEW/:OLD in PL/SQL) — values in row-level triggers; NEW is new value for insert/update, OLD is old value for update/delete.

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'error text'; — raise an error from a stored routine (MySQL syntax shown).

Note: exact delimiter rules (DELIMITER) are needed in MySQL when creating routines that contain semicolons inside.

Worked examples

All examples use MySQL-like syntax (use DELIMITER when needed). Adjust for your DBMS (Oracle PL/SQL uses different delimiters and CREATE OR REPLACE forms).

Example 1 — Stored procedure to give salary raise SQL:

CREATE TABLE employee(id INT PRIMARY KEY, name VARCHAR(20), salary DECIMAL(10,2));
INSERT INTO employee VALUES (1,'Asha',50000.00),(2,'Binu',42000.00);
DELIMITER $$
CREATE PROCEDURE give_raise(IN emp_id INT, IN pct DECIMAL(5,2))
BEGIN
  UPDATE employee SET salary = salary * (1 + pct/100) WHERE id = emp_id;
END$$
DELIMITER ;
CALL give_raise(2,10);
SELECT * FROM employee ORDER BY id;

Sample output (final SELECT):

+----+------+----------+
| id | name | salary   |
+----+------+----------+
|  1 | Asha | 50000.00 |
|  2 | Binu | 46200.00 |
+----+------+----------+

(Explanation: Binu's salary updated by 10%.)

Example 2 — Function to compute grade point SQL:

DELIMITER $$
CREATE FUNCTION grade_point(marks INT) RETURNS CHAR(1)
DETERMINISTIC
BEGIN
  DECLARE g CHAR(1);
  IF marks >= 90 THEN SET g = 'A';
  ELSEIF marks >= 75 THEN SET g = 'B';
  ELSEIF marks >= 60 THEN SET g = 'C';
  ELSE SET g = 'F';
  END IF;
  RETURN g;
END$$
DELIMITER ;
SELECT grade_point(82) AS grade;

Sample output:

+-------+
| grade |
+-------+
| B     |
+-------+

Example 3 — Trigger to maintain audit table on UPDATE SQL:

CREATE TABLE accounts(id INT PRIMARY KEY, balance DECIMAL(10,2));
CREATE TABLE accounts_audit(audit_id INT AUTO_INCREMENT PRIMARY KEY, acc_id INT, old_bal DECIMAL(10,2), new_bal DECIMAL(10,2), changed_at DATETIME);
INSERT INTO accounts VALUES (1,1000.00);
DELIMITER $$
CREATE TRIGGER log_balance_update AFTER UPDATE ON accounts
FOR EACH ROW
BEGIN
  INSERT INTO accounts_audit(acc_id, old_bal, new_bal, changed_at)
  VALUES (OLD.id, OLD.balance, NEW.balance, NOW());
END$$
DELIMITER ;
UPDATE accounts SET balance = balance + 200 WHERE id = 1;
SELECT * FROM accounts_audit;

Sample output (audit table):

+----------+--------+---------+---------+---------------------+
| audit_id | acc_id | old_bal | new_bal | changed_at          |
+----------+--------+---------+---------+---------------------+
|        1 |      1 | 1000.00 | 1200.00| 2026-11-18 00:00:00  |
+----------+--------+---------+---------+---------------------+

(Your timestamp will differ; main point: audit row inserted with OLD and NEW values.)

Notes on outputs: exact formatting (column order, datetime format) depends on client and DBMS; exam answers should show resulting rows and values.

Common mistakes

  • Using RETURN inside a procedure (only functions use RETURN).
  • Expecting NEW/OLD to be available in statement-level triggers — they are only in row-level triggers.
  • Trying to modify the same table in a row-level trigger without handling the mutating-table restriction (common in Oracle).
  • Not handling transactions: putting long-running or external I/O inside triggers can block transactions.
  • Relying on functions to have no side-effects without checking DBMS restrictions.

For your exam

Question types: definitions (define stored procedure, trigger), short notes (difference between BEFORE and AFTER; row vs statement triggers), writing code (create procedure/function/trigger), tracing code (given tables and a trigger, show final table contents), and design questions (audit, integrity enforcement using triggers vs constraints).

What full-marks answers include:

  • For definition: concise definition plus a short example of when to use it.
  • For syntax/program: full CREATE statement with correct parameter direction, delimiters if required, and a short sample invocation and result.
  • For trace problems: step-by-step explanation of the order of firing (BEFORE -> write -> AFTER), and show the final rows.
  • For design questions: mention alternatives (use constraint, use application logic, use deferred checks) and discuss transaction/privilege implications.

Quick check

  1. What keyword makes a trigger run for each affected row?
  2. Which routine type must use RETURN to deliver a single value?
  3. Name one common problem when a trigger queries the same table it modifies (Oracle term). Answers: 1. FOR EACH ROW; 2. Function; 3. Mutating table problem

Finished this topic? Mark it so your progress, study plan and readiness keep up.

Stuck on something here?