postgresql - 关系 "old"不存在

Question

当我尝试执行此脚本时，出现错误:错误:关系“旧”不存在。脚本:

update organization 
set row_status = 30;

我发现了一些这样的问题，但他们没有帮助我。我的触发器:

Create OR REPLACE function PMigrateDataToHistory()
returns trigger as $PMigrateDataToHistory$
begin

insert into organization_history
select 
  * 
from 
  OLD;

delete 
from 
  organization as org USING 
  OLD as o
where 
  o.id = org.id and
  o.version = org.version;

insert into organization
select
  n.id,
  n.created_at,
  n.updated_at,
  n.row_status,
  n.version + 1,
  n.resource
from
  NEW as n;
end;

$PMigrateDataToHistory$ LANGUAGE plpgsql;

CREATE TRIGGER TRMigrateDataToHistory AFTER update or delete ON organization
    FOR EACH ROW EXECUTE PROCEDURE PMigrateDataToHistory();

Answer 1

对于第一个 INSERT，使用类似的东西

INSERT INTO organization_history VALUES (OLD.*);

DELETE 和第二个INSERT 设计不当——一方面，这会在organization 表中造成大量不必要的变动.

最好使用BEFORE 触发器，将NEW.version 加1 并返回NEW。这会导致在将记录写入表中之前调整值。