6. Stored Procedures, Functions and Triggers

Question 1

Purpose:

Answer 1

Extend SQL with programming capabilities (control structures, error handling) and store code within the database.

Question 2

Advantages:

Answer 2

Reduce network traffic, centralise business logic, enhance security.

Question 3

Disadvantages:

Answer 3

Increased memory usage, difficult to debug, requires specialised maintenance skills.

Question 4

Stored Procedures:

Answer 4

Do not return a value (like void in Java). Executed using CALL.

Question 5

Stored Functions:

Answer 5

Return a value (like in C-style languages). Can be used anywhere an expression is allowed.

Question 6

Triggers:

Answer 6

A special type of stored routine automatically invoked by the DBMS in response to specified database changes. Unlike procedures/functions, they are event-driven.
◦ Event-Action Model: Trigger runs in response to an Event (INSERT, UPDATE, DELETE, TRUNCATE on a table). The Action is the trigger body (SQL or stored code).
◦ Triggering Time: BEFORE, AFTER, or INSTEAD OF the triggering statement.
Trigger Type:
▪ Row-level: Activated once for each row affected by the statement. Can access OLD and NEW row versions. OLD and NEW are implicit record variables; use OLD.col_name or NEW.col_name. OLD is state before, NEW is state after; applicable rows/columns vary by event (UPDATE, DELETE, INSERT).
▪ Statement-level: Activated once per statement, regardless of the number of rows affected (even zero). Cannot access OLD or NEW.
◦ Uses: Enforce complex business rules, create audit logs, maintain derived attributes, maintain archive tables. BEFORE triggers can perform complex validation or modify values before changes are written. AFTER triggers modify related data in other tables after changes are committed. INSTEAD OF triggers on views allow complex updates on non-updatable views.
◦ Issues: Invisible automatic execution, hard to troubleshoot/model performance, may increase overhead. Avoid for simple constraints. Beware of recursive triggers.