Overview
This lecture covers advanced SQL topics, focusing on SQL functions, procedures, and triggers, including their capabilities, best practices, and performance implications.
Functions and Procedural Constructs
- SQL:1999 introduced functions/procedures and control flow statements (e.g., loops, if-then-else, assignments).
- Functions/procedures can be written in SQL or external languages (C, Java), useful for complex or data-specific operations.
- Table-valued functions (added in SQL:2003) can return entire relations.
- SQL procedures can be called via the
call statement and allow overloading based on argument type/number.
- Procedural SQL supports compound statements, local variables, loops (
while, repeat, for), and conditional statements (if-then-else, case).
- Exceptions can be signaled and handled within procedures using declared handlers.
- SQL:1999 supports defining external language routines, which may offer efficiency but can raise security risks, mitigated with sandboxing or separate processes.
SQL Functions and Procedures: Examples
- Functions can encapsulate queries (e.g., department instructor count) and are used like parameterized views.
- Procedures perform actions and return results via out parameters; both can be invoked from SQL or application code.
Triggers
- Triggers are actions executed in response to insert, update, or delete operations on a table.
- Defined using
create trigger, triggers can enforce integrity, log changes, or maintain derived data.
- Triggers are classified as BEFORE (run prior to modification, can modify or validate data) or AFTER (run post-modification, often for auditing or related updates).
- Row-level triggers execute for each affected row; statement-level triggers execute once per SQL statement, using transition tables to reference affected rows.
- Triggers can reference old and new values for auditing and data integrity.
- Example: A trigger can automatically update student credits after grade changes.
Trigger Best Practices and Performance
- Use triggers for simple, isolated operations such as logging, validation, or appending metadata.
- Avoid overusing triggers, complex logic in triggers, recursive or cross-database triggers, and excessive inter-trigger calling, as these degrade maintainability and performance.
- Security and performance trade-offs exist, especially with external routines or complex trigger configurations.
Key Terms & Definitions
- Function — A stored SQL routine that returns a single value or table and can be used in queries.
- Procedure — A stored routine performing actions and possibly returning results via parameters.
- Trigger — A set of actions automatically executed when specific table events occur (insert/update/delete).
- Row-level trigger — Executes once for each affected row.
- Statement-level trigger — Executes once for the entire SQL statement, regardless of affected row count.
- Transition table — Temporary table holding affected rows for statement-level triggers.
- External routine — Function or procedure written in a language other than SQL, callable from SQL.
Action Items / Next Steps
- Review textbook page 177 for a detailed procedure example using if-then-else.
- Understand your database's specific trigger syntax and procedural language support.
- Practice writing SQL functions, procedures, and triggers.