江淼的Blog

目标：
当表alphas插入新行时，更新titles的alpha_at为NOW()
当表alphas删除行时，更新titles的alpha_at为NULL

1、安装plpgsql语言到数据库

createlang plpgsql DATABASE

2、建立一个返回为trigger的过程

CREATE OR REPLACE FUNCTION after_alphas_id() RETURNS trigger AS $BODY$
BEGIN
  IF( TG_OP='DELETE' ) THEN
    UPDATE titles SET alpha_at=null WHERE id=OLD.title_id;
  ELSE
    UPDATE titles SET alpha_at=NOW() WHERE id=NEW.title_id;
  END IF;
  RETURN NULL;
END;

$BODY$
  LANGUAGE 'plpgsql';

3、创建触发器

CREATE TRIGGER after_alphas_id
  AFTER INSERT OR DELETE
  ON alphas
  FOR EACH ROW
  EXECUTE PROCEDURE after_alphas_id();