costings_trigger.rb

class CreateLineItems < ActiveRecord::Migration[5.1]
  def change
    
    reversible do |dir|
      dir.up do
        execute <<-SQL
          CREATE OR REPLACE FUNCTION calculate_total()
            RETURNS trigger AS $$
          BEGIN
            NEW.total_cost_cents = NEW.unit_cost_cents * NEW.quantity;
            RETURN NEW;
          END; $$
          LANGUAGE PLPGSQL;

          DROP TRIGGER IF EXISTS trigger_update_total_on_insert ON line_items;
          DROP TRIGGER IF EXISTS trigger_update_total_on_update ON line_items;

          CREATE TRIGGER trigger_update_total_on_insert
            BEFORE INSERT ON line_items
              FOR EACH ROW
                EXECUTE PROCEDURE calculate_total();

          CREATE TRIGGER trigger_update_total_on_update
            BEFORE UPDATE ON line_items
              FOR EACH ROW
                WHEN (OLD.quantity <> NEW.quantity OR OLD.unit_cost_cents <> NEW.unit_cost_cents)
                  EXECUTE PROCEDURE calculate_total();
        SQL
      end
      dir.down do
        execute <<-SQL
          DROP TRIGGER IF EXISTS trigger_update_total_on_insert ON line_items;
          DROP TRIGGER IF EXISTS trigger_update_total_on_update ON line_items;
          DROP FUNCTION IF EXISTS calculate_total();
        SQL
      end
    end
  end
end