Enterprise Rails

Chapter 9. Guaranteeing Complex Relationships with Triggers

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.

Constraint Checking with Triggers

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 movies table (Figure 9-1).

Figure 9-1. Auditorium bookings in movie_showtimes; length of movie in movies

There are also a number ...