Put Your Finger on Triggers in Rails with HairTrigger

Hendra Uzia
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.

Frequently Asked Questions (FAQs) about Triggers in Rails with Hairtrigger

What is the Hairtrigger gem in Rails and why is it important?

Hairtrigger is a Ruby gem that allows you to create and manage database triggers in an easy and efficient way directly from your Rails application. It is important because it provides a way to ensure data integrity at the database level, rather than relying solely on application logic. This can be particularly useful in complex applications where multiple processes or threads may be interacting with the database concurrently.

How do I install and set up the Hairtrigger gem in my Rails application?

To install Hairtrigger, you need to add the following line to your application’s Gemfile: gem 'hairtrigger'. Then, run bundle install to install the gem. Once installed, you can use the rails generate hair_trigger_migration command to create a new migration file where you can define your triggers.

How do I create a trigger using Hairtrigger?

Creating a trigger with Hairtrigger involves defining the trigger within a migration file. Here’s an example of how to create a trigger that updates a counter cache:

create_trigger(:compatibility).on(:likes).after(:insert) do
"UPDATE posts SET likes_count = likes_count + 1 WHERE id = NEW.post_id;"
end

Can I use Hairtrigger with any database system?

Hairtrigger is designed to work with PostgreSQL, MySQL, and SQLite. However, the specific syntax and features supported may vary depending on the database system you are using.

How do I test triggers created with Hairtrigger?

Testing triggers can be done within your regular Rails testing framework. You can create records or perform actions that should trigger the trigger, and then check that the expected changes have occurred in the database.

Can I use Hairtrigger to create complex multi-statement triggers?

Yes, Hairtrigger supports the creation of complex multi-statement triggers. You can define multiple SQL statements within the block passed to the create_trigger method.

How do I remove or modify a trigger created with Hairtrigger?

To remove or modify a trigger, you can use the drop_trigger method within a migration file. To modify a trigger, you would typically drop the existing trigger and then create a new one with the updated logic.

What are some common use cases for database triggers in a Rails application?

Common use cases for database triggers include maintaining data integrity, enforcing business rules, and updating counter caches. For example, you might use a trigger to automatically update a last_modified timestamp whenever a record is updated.

Are there any downsides or risks to using database triggers in a Rails application?

While database triggers can be very powerful, they can also add complexity to your application and make debugging more difficult. It’s also important to remember that triggers operate at the database level, so they may not be aware of any application-level logic or validations.

Can I use Hairtrigger in conjunction with other database tools or gems?

Yes, Hairtrigger can be used alongside other database tools and gems. However, it’s important to ensure that your triggers do not conflict with any other database-level operations or constraints.