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:

Because a Function can be called inline, you can drop it directly into a query:

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.

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:

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.