HairTrigger¶ ↑

HairTrigger lets you create and manage database triggers in a concise, db-agnostic, Rails-y way. You declare triggers right in your models in Ruby, and a simple rake task does all the dirty work for you.

Install¶ ↑

Step 1.¶ ↑

If you are using bundler, just put hairtrigger in your Gemfile.

If you’re not using bundler, you can “gem install hairtrigger” and then put hairtrigger in environment.rb

Step 2.¶ ↑

If you plan to use model triggers (which you should ;) you’ll need to create lib/tasks/hair_trigger.rake with the following:

$VERBOSE = nil
Dir["#{Gem.searcher.find('hair_trigger').full_gem_path}/lib/tasks/*.rake"].each { |ext| load ext }

If you are unpacking the gem in vendor/plugins, this step is not needed (though you’ll then want to delete its Gemfile to avoid possible conflicts).

Usage¶ ↑

Models¶ ↑

Declare triggers in your models and use a rake task to auto-generate the appropriate migration. For example:

class AccountUser < ActiveRecord::Base
  trigger.after(:insert) do
    "UPDATE accounts SET user_count = user_count + 1 WHERE id = NEW.account_id;"
  end
end

and then:

rake db:generate_trigger_migration

This will create a db-agnostic migration for the trigger that mirrors the model declaration. The end result in MySQL will be something like this:

CREATE TRIGGER account_users_after_insert_row_tr AFTER INSERT ON account_users
FOR EACH ROW
BEGIN
    UPDATE accounts SET user_count = user_count + 1 WHERE id = NEW.account_id;
END

Note that these auto-generated create_trigger statements in the migration contain the “:generated => true” option, indicating that they were created from the model definition. This is important, as the rake task will also generate appropriate drop/create statements for any model triggers that get removed or updated. It does this by diffing the current model trigger declarations and any auto-generated triggers from previous migrations.

Manual Migrations¶ ↑

You can also manage triggers manually in your migrations via create_trigger and drop_trigger. They are a little more verbose than model triggers, and they can be more work since you need to figure out the up/down create/drop logic when you change things. A sample trigger:

create_trigger.on(:users).after(:insert) do
  "UPDATE accounts SET user_count = user_count + 1 WHERE id = NEW.account_id;"
end

Chainable Methods¶ ↑

Triggers are built by chaining several methods together, ending in a block that specifies the SQL to be run when the trigger fires. Supported methods include:

name(trigger_name)¶ ↑

Optional, inferred from other calls.

on(table_name)¶ ↑

Ignored in models, required in migrations.

for_each(item)¶ ↑

Defaults to :row, PostgreSQL/MySQL allow :statement.

before(*events)¶ ↑

Shorthand for timing(:before).events(*events).

after(*events)¶ ↑

Shorthand for timing(:after).events(*events).

where(conditions)¶ ↑

Optional, SQL snippet limiting when the trigger will fire. Supports delayed interpolation of variables.

security(user)¶ ↑

Permissions/role to check when calling trigger. PostgreSQL supports :invoker (default) and :definer, MySQL supports :definer (default) and arbitrary users (syntax: ‘user’@‘host’).

timing(timing)¶ ↑

Required (but may be satisified by before/after). Possible values are :before/:after.

events(*events)¶ ↑

Required (but may be satisified by before/after). Possible values are :insert/:update/:delete/:truncate. MySQL/SQLite only support one action per trigger, and don’t support :truncate.

all¶ ↑

Noop, useful for trigger groups (see below).

Trigger Groups¶ ↑

Trigger groups allow you to use a slightly more concise notation if you have several triggers that fire on a given model. This is also important for MySQL, since it does not support multiple triggers on a table for the same action and timing. For example:

trigger.after(:update) do |t|
  t.all do # every row
    # some sql
  end
  t.where("OLD.foo <> NEW.foo") do
    # some more sql
  end
  t.where("OLD.bar <> NEW.bar") do
    # some other sql
  end
end

For MySQL, this will just create a single trigger with conditional logic (since it doesn’t support multiple triggers). PostgreSQL and SQLite will have distinct triggers. This same notation is also used within trigger migrations. MySQL does not currently support nested trigger groups.

Testing¶ ↑

To stay on top of things, it’s strongly recommended that you add a test or spec to ensure your migrations match your models. This is as simple as:

assert HairTrigger::migrations_current?

This way you’ll know if there are any outstanding migrations you need to create.

Warnings and Errors¶ ↑

There are a couple classes of errors: declaration errors and generation errors/warnings.

Declaration errors happen if your trigger declaration is obviously wrong, and will cause a runtime error in your model or migration class. An example would be “trigger.after(:never)”, since :never is not a valid event.

Generation errors happen if you try something that your adapter doesn’t support. An example would be something like “trigger.security(:invoker)” for MySQL. These errors only happen when the trigger is actually generated, e.g. when you attempt to run the migration.

Generation warnings are similar but they don’t stop the trigger from being generated. If you do something adapter-specific supported by your database, you will still get a warning ($stderr) that your trigger is not portable. You can silence warnings via “HairTrigger::Builder.show_warnings = false”

You can validate your triggers beforehand using the Builder#validate! method. It will throw the appropriate errors/warnings so that you know what to fix, e.g.

> User.triggers.each(&:validate!)

HairTrigger does not validate your SQL, so be sure to test it in all databases you want to support.

Gotchas, Known Issues¶ ↑

• HairTrigger does not check config.active_record.timestamped_migrations, it always assumes it is true. As a workaround, you can rename the migration.

• As is the case with ActiveRecord::Base.update_all or any direct SQL you do, be careful to reload updated objects from the database. For example, the following code will display the wrong count since we aren’t reloading the account:

  a = Account.find(123)
  a.account_users.create(:name => 'bob')
  puts "count is now #{a.user_count}"
  

• For repeated chained calls, the last one wins, there is currently no merging.

• If you want your code to be portable, the trigger actions should be limited to INSERT/UPDATE/DELETE/SELECT, and conditional logic should be handled through the :where option/method. Otherwise you’ll likely run into trouble due to differences in syntax and supported features.

Compatibility¶ ↑

• Rails 2.3.x

• Postgres 8.0+

• MySQL 5.0+

• SQLite 3.0+

Version History¶ ↑

• 0.1.4 Compatibility tracking, fixed Postgres return bug, ensure last action has a semicolon

• 0.1.3 Better error handling, Postgres 8.x support, updated docs

• 0.1.2 Fixed Builder#security, updated docs

• 0.1.1 Fixed bug in HairTrigger.migrations_current?, fixed up Gemfile

• 0.1.0 Initial release

Copyright¶ ↑

Copyright © 2011 Jon Jensen. See LICENSE.txt for further details.