Stored Procedures and Triggers — Code Inside the Database
Most of ReadingRoom's logic lives in C#. But some jobs sit better next to the data: a heavy report that crunches thousands of loans, or an audit record that must be written no matter which program deletes a loan. Databases can hold code for exactly these jobs. (Examples use SQL Server's T-SQL, common in .NET shops; PostgreSQL has the same ideas with different syntax.)
The rule
A procedure runs when you call it; a trigger runs when data changes.
A stored procedure is named, saved SQL with parameters:
CREATE PROCEDURE dbo.TopBooks @Since date
AS
BEGIN
SELECT TOP 10 b.Title, COUNT(*) AS Loans
FROM Loans l JOIN Books b ON b.Id = l.BookId
WHERE l.LoanedOn >= @Since
GROUP BY b.Title
ORDER BY COUNT(*) DESC;
END;You run it with EXEC dbo.TopBooks @Since = '2025-01-01';. From C#, Dapper calls it directly:
var top = await connection.QueryAsync<BookCount>(
"dbo.TopBooks", new { Since = since },
commandType: CommandType.StoredProcedure);A trigger is attached to a table and fires on INSERT, UPDATE or DELETE. Nobody calls it:
CREATE TRIGGER trg_Loans_Audit ON Loans
AFTER DELETE
AS
BEGIN
INSERT INTO LoanAudit (LoanId, DeletedAt)
SELECT Id, SYSUTCDATETIME() FROM deleted;
END;Inside a SQL Server trigger, the deleted and inserted pseudo-tables hold the affected rows. A single statement can touch many rows, so a trigger must handle sets, not one row.
Why use them: less data sent over the network, one shared rule for every program using the database. Why be careful: the logic is hidden from anyone reading the C# code, harder to test, and tied to one database vendor.
The shortcut
Ask "who starts it?" If your app decides when it runs → a procedure (or just C#). If it must happen whenever the data changes, whoever changes it → a trigger.
The monthly report
The librarian clicks "Report". The app starts it, so it is a procedure.
The trap: writing a trigger that assumes one row, such as reading SELECT @Id = Id FROM deleted. A bulk delete of 50 loans would audit only one.
Drills
Answer fast — trust the rule.
How does a trigger run?
In a SQL Server AFTER DELETE trigger, where are the removed rows?
A report runs when a librarian clicks a button. What fits best?
Which is a genuine downside of heavy logic in stored procedures?
Remember
- A procedure runs when you call it; a trigger runs when data changes.
- Ask "who starts it?" — the app, or the data change?
- Triggers handle sets of rows; keep database logic small and visible.