Rails SQL Triggers

Library which adds SQL Triggers to Rails. Adds create_trigger and drop_trigger to the ActiveRecord::ConnectionAdapters::AbstractAdapter (which makes them available to migrations) and adds support for dumping triggers in the ActiveRecord::SchemaDumper.

Installation

To install:

gem install rails_sql_triggers

Then add the following to your Rails config/environment.rb:

require_gem 'rails_sql_triggers'
require 'rails_sql_triggers'

Usage

You can then use create_trigger and drop_trigger in your migrations. For example:

class CreatePersonChangeTrigger < ActiveRecord::Migration
  def self.up
    create_table :person_changes do |t|
      t.integer :person_id, :null => false
      t.string :first_name, :null => false
      t.string :last_name, :null => false
      t.string :email, :null => false
      t.datetime :effective_start
      t.datetime :effective_end
      t.integer :deleted_flag, :null => false, :default => 0
      t.string :updated_by
    end
    create_trigger :person_change_trig, :people, [:insert, :update], <<EOSQL

DECLARE @person_id INT DECLARE @first_name VARCHAR(255) DECLARE @last_name VARCHAR(255) DECLARE @email VARCHAR(255) DECLARE @effective_start DATETIME DECLARE @updated_by VARCHAR(255) SELECT @person_id = (SELECT id FROM inserted) SELECT @first_name = (SELECT first_name FROM inserted) SELECT @last_name = (SELECT last_name FROM inserted) SELECT @email = (SELECT email FROM inserted) SELECT @effective_start = (SELECT updated_at FROM inserted) SELECT @updated_by = (SELECT updated_by FROM inserted) UPDATE person_changes SET effective_end = @effective_start

WHERE person_id = @person_id AND effective_end IS NULL

INSERT INTO person_changes (person_id, first_name, last_name, email, effective_start, updated_by)

VALUES (@person_id, @first_name, @last_name, @email, @effective_start, @updated_by)

EOSQL

  end
  def self.down
    drop_trigger :person_change_trig
  end
end

This extension also adds support for triggers in the ActiveRecord::SchemaDumper class.

The following drivers are supported:

SQL Server

Known Issues

• Drivers not mentioned above are not supported.

If you find any issues please send an email to stewbawka@gmail.com .

Contributing

If you would like to implement trigger support for other adapters then please drop me an email. Better yet, write up the adapter modifications and send them to me. :-)