How to use PostgreSQL triggers?

Question

I am trying to use PostgreSQL triggers in my rails app. So I tried using this migration where execution of triggers is supposedly easy:

-- class AddTriggersToProducts < ActiveRecord::Migration
  def self.up
    table :products
    execute %q{
        create trigger trig1 before insert on products for each row
        begin 
        price = price + 5
        end;
        }
  end

  def self.down
    execute 'DROP TRIGGER trig1'
  end
end

But this didn't change anything. I don't know where to write the procedure or function if I am going to use one here ...

Answer 1

"Creating a trigger" consists of two steps in PostgreSQL:

1.) Create a trigger function - with special return value trigger:

CREATE FUNCTION trg_update_prod_price()
  RETURNS trigger AS
$func$
BEGIN
   NEW.price := NEW.price + 5;
   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Multiple triggers can use the same trigger function.

2.) Create a trigger calling an existing trigger function:

CREATE TRIGGER update_prod_price
BEFORE INSERT ON products
FOR EACH ROW EXECUTE PROCEDURE trg_update_prod_price();

To "drop the trigger" (meaning the trigger function), you have to first drop all triggers referencing it and then drop the trigger function itself.

DROP TRIGGER update_prod_price ON products;
DROP FUNCTION trg_update_prod_price();

If you drop a table, all attached triggers are dropped with it. No need to drop those separately.

Answer 2

Does something like this work? Creating a function and then executing the function for the trigger:

   def self.up 
     execute %q{
       create or replace function update_price() returns trigger as $$
         begin
           NEW.price := NEW.price + 5;
           return NEW;
         end;
       $$ language plpgsql }

      execute %{ create trigger trig1 before insert on products for each row execute function update_price()}
  end

Answer 3

The hair_trigger gem is a nice way to manage the creation of triggers.

Here is an example from hair_trigger's docs:

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

  trigger.after(:update).of(:name) do
    "INSERT INTO user_changes(id, name) VALUES(NEW.id, NEW.name);"
  end
end