Ruby

Put Your Finger on Triggers in Rails with HairTrigger

Hendra Uzia
Share

Database Configuration Icon. Vector Illustration

HairTrigger enables Ruby on Rails applications to create and manage database triggers in a concise and database agnostic way. Without it, implementing database triggers in a Rails application would require database specific knowledge on creating stored procedures with desired SQL statements to do database operations.

In this article, you will be given a short introduction to database triggers, along with a comparison between using Active Record callbacks and database triggers. This article will also explain the key points of implementing database triggers using HairTrigger. For complete information of the HairTrigger API, please refer to the official documentation.

To have a good starting point, you will be provided a use case of implementing database triggers using HairTrigger. The use case demonstrates a denormalization strategy that requires implementing database triggers to aggregate and synchronize data between tables. The full source code of the implementation is available on Github as a reference.

Database Triggers at a Glance

A database trigger is a set of SQL statements put in a stored procedure that will be automatically executed when a specific event occurs on a given table or view in a database. This section will not discuss implementation details of database triggers, rather it will provide a high-level overview of what we will try to implement, and when not to use a database trigger.

Denormalization Using Database Triggers

We will be using a denormalization strategy to demonstrate database trigger implementation. There are 2 tables involved: clips and videos. Video can have many clips and a clip can only belong to a single video. The denormalization involves summarizing the column duration of table clips into a matching column duration of the videos table.

To avoid calculating the total video clip duration every single time, it would be better to denormalize the clip duration. The value of clip duration also rarely changes, which makes it a good candidate for denormalization using a database trigger.

A database trigger will be used to calculate the total of video clip duration on every clip duration update and store it inside video duration. Other times that require updating video duration includes creating and deleting the clip.

Here are the events that need to be considered when you create database trigger for the clips table:

  • After creating a clip, update video duration.
  • After updating a clip duration, update video duration.
  • After deleting a clip, update video duration.

The Pitfalls of Database Triggers

As a side note, please be very careful not to introduce a circular update. That means, avoid creating a trigger that updates a column which also triggers an update of the former column.

Another circumstance where you should avoid using database triggers is when the affected row changes frequently, this might introduce a “data update storm”. If needed, you should add a precondition to the trigger that ensures the trigger fires only when a specific column is updated.

Database Triggers vs Active Record Callbacks

While database triggers are hooks into the life cycle of a table or view, Active Record Callbacks are hooks into the life cycle of Active Record objects. Database triggers should not be considered as a complete replacement to Active Record callbacks. Instead, triggers should be considered as a complement to Active Record callbacks.

When to Use Database Triggers

Triggers should be used in a situation where you need a specific database feature that is not available from an Active Record callback. As long as it doesn’t introduce a circular update or data update storm, as mentioned in the previous section, it is OK to use a database trigger. Here are some key points that you can use when deciding to implement database triggers in your Rails application:

  • Requires atomic SQL operations.
  • Overcoming a race condition, e.g. aggregating data.
  • Enforcing referential integrity across databases.
  • Logging or auditing database records.

When to Use Active Record Callbacks

On the other hand, Active Record callbacks should be used in a situation where it doesn’t need a specific database feature and is available from Active Record. It should also be noted that you must be very careful not to introduce circular callbacks, just like the circular update issue when using triggers. Here are some cases where it is better to use Active Record callbacks rather than triggers:

  • Requires debugging, it’s plain Ruby anyway.
  • Hooks into Active Record specific callback, such as before validation.
  • SQL operation that doesn’t introduce a race condition.
  • SQL operation that requires specific Ruby/Rails features.

That said, this might not be a complete comparison between the two, but it should give you a head start on choosing the right technology before you start implementing it.

Database Triggers using HairTrigger

Let’s look at how to manage triggers using the HairTrigger gem, specifically with the use case of denormalization and data aggregation. The demonstration showed here users the following technology:

  • Ruby 2.3.0
  • Rails 4.2.5.1
  • PostgreSQL 9.5.0

Any implementations in the next following sections have not been tested against other environment. If you havea different setup, please consult the HairTrigger compatibility list before moving on to the next section.

Installation

Put the gem into your Gemfile:

gem "hairtrigger"

And run the following command from your application root path:

bundle install

That’s it, you’re good to go.

Implementation

To implement our use case, it is assumed that all required fields for the demonstration are already put in place. The next step is to add the trigger, let’s revisit the plan for our database triggers:

  • After creating a clip, update video duration.
  • After updating a clip duration, update video duration.
  • After deleting a clip, update video duration.

You can see that the statement following all events is exactly the same, that is, update video duration. Therefore, we can create a method that returns SQL statement to be used in all events:

# app/models/clip.rb
def self.update_video_duration_sql
  <<-SQL
    UPDATE videos SET duration = (
      SELECT sum(clips.duration)
      FROM clips
      WHERE clips.video_id = videos.id
    )
  SQL
end

The update condition can be done by using special variables, which are NEW and OLD. Because some events hold different variables, you should put the update condition inside each trigger. Please refer to your database documentation for special variables usage.

After creating a clip, update video duration:

# app/models/clip.rb
trigger.after(:create) do
  <<-SQL
    #{ self.update_video_duration_sql }
    WHERE videos.id = NEW.video_id
  SQL
end

After updating a clip duration, update video duration:

# app/models/clip.rb
trigger.after(:update).of(:duration) do
  <<-SQL
    #{ self.update_video_duration_sql }
    WHERE videos.id = NEW.video_id
  SQL
end

After deleting a clip, update video duration:

# app/models/clip.rb
trigger.after(:delete) do
  <<-SQL
    #{ self.update_video_duration_sql }
    WHERE videos.id = OLD.video_id
  SQL
end

Database Migration

To create the database migration, run the following command:

bundle exec rake db:generate_trigger_migration

The above Rake task, which is provided by the HairTrigger gem, will create the necessary migration for your database triggers. After that, apply the database migration by running the following command.

bundle exec rake db:migrate

Every time you change something inside the trigger block, you should run the trigger migration generation Rake task, or else, no migration will be created. How this works is that it will diff the SQL statements inside the trigger block before and after the code update, therefore it can determine if a new migration should be created.

Conclusion

This concludes the implementation of database triggers using HairTrigger. HairTrigger provides other methods that allow easy integration and management of triggers, please visit the official documentation for a complete reference. After reading this article, I hope you have a better understand the benefits of using triggers, avoid implementation pitfalls, and are able to apply it properly in your Rails application using HairTrigger.

CSS Master, 3rd Edition