SQL · SQL · College and University

SQL Stored Procedures and Triggers

Writing stored procedures, functions, and triggers, each explained with a short example.

30 cards · basic cards · AI-written, checked twice. Edit anything.

Opens in your browser, no account. Get the iPhone and iPad app · Android app

What is a stored procedure in SQL?

Show answer

A reusable block of SQL statements stored in the database and executed by name

Write the basic syntax to create a stored procedure

Show answer

CREATE PROCEDURE name AS BEGIN SELECT * FROM table; END;

What is a user-defined function in SQL?

Show answer

A reusable code block that accepts parameters and returns a single value or result set

Difference: scalar function vs table-valued function

Show answer

Scalar returns one value; table-valued returns multiple rows and columns

Write basic syntax for a scalar function that doubles an integer

Show answer

CREATE FUNCTION double(n INT) RETURNS INT AS BEGIN RETURN n * 2; END;

Write basic syntax for a table-valued function

Show answer

CREATE FUNCTION nameOfFunction() RETURNS TABLE AS RETURN (SELECT * FROM table);

What is a trigger in SQL?

Show answer

An automatic action executed in response to a specified event (INSERT, UPDATE, DELETE) on a table

Name the three timing events when a trigger can fire

Show answer

BEFORE (before the event), AFTER (after the event), INSTEAD OF (replaces the event)

What is the difference between BEFORE and AFTER triggers?

Show answer

BEFORE fires before the change is applied; AFTER fires after the change is committed

What does an INSTEAD OF trigger do?

Show answer

Replaces the triggering event with a different set of actions instead of executing the original event

Write basic syntax for an INSERT trigger that logs to an audit table

Show answer

CREATE TRIGGER auditInsert AFTER INSERT ON table BEGIN INSERT INTO audit VALUES(NEW.id, NOW()); END;

Write basic syntax for an UPDATE trigger

Show answer

CREATE TRIGGER auditUpdate AFTER UPDATE ON table BEGIN INSERT INTO log VALUES(OLD.id, NEW.value); END;

Write basic syntax for a DELETE trigger that archives deleted rows

Show answer

CREATE TRIGGER archiveDelete AFTER DELETE ON table BEGIN INSERT INTO archive SELECT * FROM OLD; END;

What is a parameter in a stored procedure?

Show answer

A variable passed into the procedure at execution time to customize its behavior

Difference: INPUT parameter vs OUTPUT parameter

Show answer

INPUT passes data into the procedure; OUTPUT receives data from the procedure back to the caller

15 more cards in the app