如何使用PostgreSQL触发器? [英] How to use PostgreSQL triggers?

问题描述

我正在尝试在Rails应用程序中使用PostgreSQL触发器.因此，我尝试使用这种迁移来执行触发器，该迁移据说很容易:

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 ...

推荐答案

创建触发器"在PostgreSQL中由两个步骤组成:

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

1.)创建一个 触发函数 -具有特殊返回值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.)创建 触发 调用现有的触发函数:

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.

这篇关于如何使用PostgreSQL触发器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！