PostgreSQL触发器拼接生成animal_id失败问题求助

方案1错误原因 #

你用SET NEW.animal_id = ...是语法错误，在PL/pgSQL触发器函数里，直接给NEW对象的字段赋值就行，不需要加SET关键字。

方案2错误原因 #

这个方案有两个问题：

• 用INSERT INTO capt_trial(animal_id)会插入一条新记录，而不是修改当前正在处理的插入/更新行，触发器应该通过NEW对象来修改当前行。
• 拼接时直接写species，没加NEW.前缀，PL/pgSQL没法识别这个未限定的列名，必须用NEW.species来引用当前行的字段值。

正确的解决方法 #

1. 创建正确的触发器函数 #

CREATE OR REPLACE FUNCTION create_short_animal_id()
RETURNS TRIGGER AS $$
BEGIN
  -- 直接拼接字段值赋值给NEW.animal_id
  NEW.animal_id := CONCAT(NEW.species, '-', NEW.capture_year, '-', NEW.tag_num);
  RETURN NEW; -- 必须返回NEW，触发器才能应用这个修改
END;
$$ LANGUAGE plpgsql;

2. 创建BEFORE触发器 #

需要创建BEFORE INSERT OR UPDATE触发器，这样在记录插入或更新前就完成animal_id的生成：

CREATE TRIGGER trigger_generate_animal_id
BEFORE INSERT OR UPDATE ON capt_trial
FOR EACH ROW EXECUTE FUNCTION create_short_animal_id();

测试验证 #

插入一条测试数据：

INSERT INTO capt_trial(species, capture_year, tag_num, tag_col)
VALUES('ANAM', 2011, 10, 'R');

查询表数据：

SELECT * FROM capt_trial;

会得到符合要求的animal_id：

内容的提问来源于stack exchange，提问作者alexhett