O'Reilly logo

Enterprise Rails by Dan Chak

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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).

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

There are also a number of cases to be ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required