Course Preview

Stored Procedures, Triggers, and Database Programming

Stored procedures and functions keep callable SQL programs inside a database. Triggers run database code automatically when specified data or schema events occur, which can centralize rules but also hide work from the statement that caused it.

itDatabases and data storage | OpenSkills.info

Course pathWalk it in order

Look it upDip in anytime

Go furtherLeaves this page

Don't Panic — Stored Procedures, Triggers, and Database Programming

Database programming puts executable logic beside the data it reads and changes. That sounds like the database has acquired a side project. More often, it gives one carefully bounded operation a home where every client reaches the same rule.

There are three program units. A stored procedure is a named operation: a client calls it, supplies parameters, and receives a result or error. A stored function returns a value or table-shaped result inside an expression or query. A trigger runs when a database event occurs, without an application call asking for it.

That difference is the mental model to keep. Procedure calls are visible in application flow. Trigger calls are an implicit branch from an insert, update, or delete. The database may run a before trigger, make the requested change, run an after trigger, and then commit or roll back the surrounding transaction. The innocent-looking update has acquired a small entourage.

Timing says when a trigger runs. Granularity says whether it runs once per row or once per statement. Those are separate choices, and bulk updates make the distinction more than academic. A statement can affect zero, one, or many rows. Code expecting a single row has chosen an adventure where the database writes the ending.

The other important boundary is privilege. Invoker rights use the caller authority. Definer rights use the owner or named definer authority. That lets a routine expose one narrow operation without direct table access for every caller. It also means dynamic SQL, unqualified names, and broad owner privileges deserve the sort of attention normally reserved for a loose floorboard.

Before adding procedural code, check whether a primary key, foreign key, unique constraint, check constraint, default, or generated value states the rule directly. Use a procedure for a stable named operation. Use a function for reusable SQL computation. Use a trigger when every matching event path must carry the reaction. Keep external work outside the transaction; locks do not enjoy waiting for the rest of the universe.

Start with the Intro for the execution path and security model. Use the Slides to compare program units and trigger choices. Keep the Cheatsheet nearby when reviewing timing, row data, deployment, and operational signals. The Practice Reference and exercise turn the model into a small PostgreSQL routine and audit trigger, where the implicit work is visible on purpose.

Where this skill leads

Relevant careers

See how this topic contributes to broader role-level skill maps.

Database Administratorcore

Sources