house.rb

class House
  has_many :photos
end

photo.rb

class Photo < ActiveRecord::Base
  belongs_to :house

  trigger.after(:insert) do
    <<-SQL
      IF NEW.cover != 't' THEN
        IF (SELECT COUNT(*) FROM photos WHERE photos.house_id = NEW.house_id) = 0 THEN
          UPDATE photos
          SET cover = 't'
          WHERE photos.id = NEW.id;
        ELSE
          UPDATE photos
          SET cover = 'f'
          WHERE 
            photos.house_id = NEW.house_id
            AND photos.id <> NEW.id;
        END IF;
      END IF;
    SQL
  end

  trigger.after(:update).of(:cover) do
    <<-SQL
      IF NEW.cover = 't' THEN
        UPDATE photos
        SET cover = 'f'
        WHERE
          photos.house_id = NEW.house_id
          AND photos.id <> NEW.id;
      END IF;
    SQL
  end

  trigger.after(:delete) do
    <<-SQL
      IF OLD.cover = 't' THEN
        UPDATE photos
        SET cover = 't'
        WHERE photos.id = (
          SELECT photos.id 
          FROM photos 
          WHERE photos.house_id = OLD.house_id 
          ORDER BY created_at DESC 
          LIMIT 1
        );
      END IF;
    SQL
  end
end