Is validation in your models or form objects enough to ensure integrity of the data? Well, seems like you can’t really persist a record when the data is not valid unless you intentionally try to bypass validation using
save: false option when calling
save or using
update_column. What about uniqueness validation?
A classic example would be a unique
Anatomy of PostgreSQL triggers and procedures
PostgreSQL trigger is like a callback: it’s a function that is called on specific event:
truncate in case of tables and views and for views you can also run a function
instead of those events. Triggers can be run either
for each row (tables only) and
for each statement (both tables and views). The difference between them is quite simple:
for each row is run for every modified row and
for each statement is run only once per statement. The important thing to keep in mind regarding
for each row is that you have a reference to the row being modified, which will be essential in upcoming example.
\h CREATE TRIGGER; from psql we can get a generalized syntax for creating triggers:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
You can also add a condition for running triggers and timing option, which I’m not going to discuss in greater details, you can find more about them in official docs.
function_name? It’s a user-defined function returning
trigger. Here’s a dummy example to give an idea about the syntax for defining functions:
1 2 3 4 5 6 7 8 9
We started with defining
dummy function taking no arguments returning type of trigger. Next, we have a
DECLARE block where we declare temporary variables, in our case it’s
some_integer_variable of type
BEGIN / END block we define the actual function body: we assign a dummy value to
some_integer_variable variable using
:= operator and then we do some assignment using implicit
NEW variable which is basically a row referenced by given statement. This variable is available only when running a trigger
for each row, otherwise it will return
NULL. Any trigger has to return either a row or
NULL - in this example we return
NEW row. At the end we have a declaration of writing function in
Let’s take a look at some real world code to see triggers in action.
Using triggers for data integrity
A good example where we could use a trigger for more complex validation could be a calendar event: we need to ensure that no other event exists between some
begins_at time and
finishes_at time. We should also scope it only to a given calendar and exclude id of event being updated - when creating new events it wouldn’t matter, but without excluding id we wouldn’t be able to update any event. So what we actually want to achieve is to create a trigger that will be run
before insert or update on events table
for each row.
Let’s start with generating
Calendar model and
Event model with reference to
and also generate extra migration adding trigger and procedure ensuring that only one event can be created for given period of time for a calendar:
add the SQL code :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
validate_event_availability function performs query to count all events that are between given time period for specified calendar excluding own id (so that the row being updated is not considered here, which would prevent updating any event). If any other event is found, the exception is raised with an error message -
% characters are used for interpolation of
finishes_at attributes. If no other event is found, we simply return the row.
We want to define a trigger running this function before creating any new event or updating existing ones, so we need to run it
BEFORE INSERT OR UPDATE
FOR EACH ROW.
It might be a good idea to switch also to
:sql schema format - the standard
:ruby format can’t handle triggers at this point. Add this line in
Now we can run migrations:
After changing the schema format, new
structure.sql file should be created. It’s not going to look that nice like
schema.rb, but at least it contains all the details. Let’s try creating some events from
1 2 3 4
Creating first event obviously works, but what’s going to happen when we try to create an event with exactly the same dates?
1 2 3
Awesome (haha, doesn’t happen that often to be happy when an error occurs ;)), that’s exactly what we wanted to achieve - the trigger keeps our data safe making sure that we won’t have any duplicated events or events covering the same time period. The last thing we should do is to mimic such validation and add it to form object or model for better user experience, but it’s beyond the scope of this article. It’s going to duplicate some logic between the code in the application and the database, but in this case there’s no way to DRY it up.
PostgreSQL triggers and procedures are not something that you will often want to use in Rails applications, but sometimes there’s no other solution, especially when you have more complex rules for data integrity. In such cases, triggers and procedures are the right tool for the job.