In this chapter, we’ll go over an advanced technique for mapping tricky real-world referential integrity constraints to the data layer. This technique uses database triggers, which allow you to run arbitrary code to check that new data is valid before inserting or updating rows. We’ll write a stored procedure that ensures an auditorium isn’t double-booked and create a trigger that runs our procedure whenever a movie showtime is added or changed. I’ll describe the basic structure of a function in Postgtres’s procedural language PL/pgSQL, so you can start building your own functions for a variety of uses, and cover the special properties of trigger functions.
As just noted, stored procedures that can be triggered when certain conditions in the
database are met can allow you to check for and guarantee arbitrarily
complex relationships. A number of times throughout this book, the issue
of double-booking an auditorium has come up. This problem is not easy to
avoid with simple foreign key or check constraints because the problem
spans multiple tables. Auditorium bookings are recorded in the
movie_showtimes table, but the length of a
movie is recorded in the
There are also a number of cases to be ...