Functions, Triggers & Global Temp Tables

# Functions, Triggers & Global Temp Tables You've now seen Stored Procedures, Views, and Temp Tables -- the three most common ways to package and stage SQL logic. Two more database objects round out the picture: standalone **Functions** and **Triggers**. Plus one more wrinkle on Temp Tables you haven't seen yet: the *global* variant. ## Functions vs. Stored Procedures A **Function** looks similar to a Stored Procedure -- both package reusable SQL logic -- but they're used very differently: | | Function | Stored Procedure | |---|---|---| | Returns | A single value or a table | Zero, one, or multiple result sets | | Called from | Inline, inside a `SELECT`/`WHERE` (like an expression) | Standalone, via `CALL` | | Can run DDL / transaction control (`COMMIT`, `CREATE TABLE`, ...) | No | Yes | Because a Function can be called inline, you can drop it directly into a query: ```sql SELECT CustomerId, Total, calculate_tax(Total) AS Tax FROM Invoice; ``` A Stored Procedure can't be used this way -- it has to be called on its own via `CALL procedure_name(...)`, and its result (if any) is a separate result set, not a value you can use inside another expression. ## Triggers A **Trigger** is a block of SQL that fires **automatically** when a specified event happens on a table -- you never call it directly. ```sql CREATE TRIGGER log_salary_change AFTER UPDATE ON Employee FOR EACH ROW BEGIN INSERT INTO EmployeeAudit (EmployeeId, OldValue, NewValue, ChangedAt) VALUES (OLD.EmployeeId, OLD.Salary, NEW.Salary, NOW()); END; ``` Triggers fire on `INSERT`, `UPDATE`, or `DELETE`, and can be timed: - **BEFORE** -- runs before the change is applied (useful for validation or normalizing a value before it's saved). - **AFTER** -- runs after the change is applied (useful for auditing, or updating a dependent column elsewhere). - **INSTEAD OF** -- replaces the change entirely (common on views that aren't directly updatable). Common use cases: audit logging (who changed what, and when), keeping a derived/denormalized column in sync automatically, and enforcing business rules too complex for a plain `CHECK` constraint. ## Global vs. Local Temp Tables The Temp Tables video only showed the `#` prefix -- a **local** temp table, visible only to the session that created it. SQL Server also supports a **global** temp table, prefixed `##`, which stays visible to *every* session until the last session referencing it closes: ```sql CREATE TABLE #local_staging (...); -- only this session can see it CREATE TABLE ##shared_staging (...); -- every session can see it ``` Global temp tables are rare in practice -- local temp tables cover the vast majority of real use cases -- but the distinction shows up often enough in interviews and reference material to be worth knowing. ## Recap - A **Function** returns a value/table and can be used inline in a query; a **Stored Procedure** is called standalone and can run DDL/transaction control that a Function can't. - A **Trigger** fires automatically on `INSERT`/`UPDATE`/`DELETE`, timed `BEFORE`, `AFTER`, or `INSTEAD OF` -- common for auditing and keeping derived data in sync. - A **local** (`#`) temp table is session-only; a **global** (`##`) temp table is visible across sessions until nobody references it anymore.