关系“旧”不存在 [英] relation "old" does not exist

问题描述

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

When i try execute this script, i get an error: ERROR: relation "old" does not exist. Script:

update organization 
set row_status = 30;

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

I found some question like this, but their dont help me. My trigger:

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();

推荐答案

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

For the first INSERT, use something like

INSERT INTO organization_history VALUES (OLD.*);

DELETE 和第二个 INSERT 是构思错误的–首先，这会在 organization 表中造成很多不必要的混乱。

The DELETE and the second INSERT are ill-conceived – for one, this will cause a lot of unnecessary churn in the organization table.

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

It would be much better to use a BEFORE trigger, add 1 to NEW.version and return NEW. This would cause the values to be adjusted before the record is written to the table.

这篇关于关系“旧”不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！