ActiveRecord + MySQL Trigger

add_trigger_to_room_chat.rb

class AddTriggerToClanChat < ActiveRecord::Migration

  # MySQL のトリガーは同テーブルへの変更が不可能なため実行時にエラーとなる
  def up
    execute <<-SQL
      CREATE TRIGGER trigger_delete_chat AFTER INSERT ON room_chats
      FOR EACH ROW
      BEGIN
        DECLARE num INT;
        SELECT COUNT(*) INTO num FROM room_chats WHERE room_id = new.room_id;
        IF num >= 250 THEN
          DELETE FROM room_chats WHERE room_id = new.room_id LIMIT 50;
        END IF;
      END
    SQL
  end

  def down
    execute <<-SQL
      DROP TRIGGER trigger_delete_chat
    SQL
  end

end