Writing stored procedures, functions, and triggers, each explained with a short example.
30 cards · basic cards · AI-written, checked twice. Edit anything.
- What is a stored procedure in SQL?
- A reusable block of SQL statements stored in the database and executed by name
- Write the basic syntax to create a stored procedure
- CREATE PROCEDURE name AS BEGIN SELECT * FROM table; END;
- What is a user-defined function in SQL?
- A reusable code block that accepts parameters and returns a single value or result set
- Difference: scalar function vs table-valued function
- Scalar returns one value; table-valued returns multiple rows and columns
- Write basic syntax for a scalar function that doubles an integer
- CREATE FUNCTION double(n INT) RETURNS INT AS BEGIN RETURN n * 2; END;
- Write basic syntax for a table-valued function
- CREATE FUNCTION nameOfFunction() RETURNS TABLE AS RETURN (SELECT * FROM table);
- What is a trigger in SQL?
- 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
- BEFORE (before the event), AFTER (after the event), INSTEAD OF (replaces the event)
- What is the difference between BEFORE and AFTER triggers?
- BEFORE fires before the change is applied; AFTER fires after the change is committed
- What does an INSTEAD OF trigger do?
- 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
- CREATE TRIGGER auditInsert AFTER INSERT ON table BEGIN INSERT INTO audit VALUES(NEW.id, NOW()); END;
- Write basic syntax for an UPDATE trigger
- 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
- CREATE TRIGGER archiveDelete AFTER DELETE ON table BEGIN INSERT INTO archive SELECT * FROM OLD; END;
- What is a parameter in a stored procedure?
- A variable passed into the procedure at execution time to customize its behavior
- Difference: INPUT parameter vs OUTPUT parameter
- INPUT passes data into the procedure; OUTPUT receives data from the procedure back to the caller