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();
For the first INSERT, use something like
INSERT INTO organization_history VALUES (OLD.*);
The DELETE and the second INSERT are ill-conceived – for one, this will cause a lot of unnecessary churn in the organization table.
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments