How to use PostgreSQL triggers?

Question

I am trying to use PostgreSQL triggers in a rails application. So I tried using this migration, where the 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 that didn’t change anything. I do not know where to write a procedure or function if I am going to use it here ...

Answer 1

Creating a trigger consists of two steps in PostgreSQL:

1.) Create a trigger function - using a 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;

.

2.) , :

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

" " ( ), , , .

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

, . .

Answer 2

- ? , :

   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

hair_trigger gem - .

hair_trigger:

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