CODEWITHSUNDEEP
postgresqltriggers
K.Kirill
K.Kirill

Reputation: 13

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

Upvotes: 0

Views: 2089

Answers (3)

Shipan Liu
Shipan Liu

Reputation: 1

@Laurenz Albe is right, about using "DELETE" you have to use "BEFORE" not "AFTER" here are my examples(using postgresql):

create table if not exists v08_orders (
order_id serial primary key,
order_date DATE,
customer_name varchar(50));



create table if not exists v08_order_logs(
log_id serial primary key,
order_id INT,
log_date TIMESTAMP default CURRENT_TIMESTAMP,
log_text TEXT,
FOREIGN KEY (order_id) REFERENCES v08_orders (order_id) on delete set null);

create or replace function func_delete_order() returns trigger as $$ begin insert into v08_order_logs(order_id, log_text) values(OLD.order_id, 'an order is deleted ar: ' || CURRENT_TIMESTAMP); return OLD; end; $$ language plpgsql;

CREATE or replace TRIGGER order_delete_trigger
before DELETE ON v08_orders
FOR EACH ROW
execute function func_delete_order();

so every time you delete an item from the order, before deleting actually happens, an action of adding one log in the order_logs table will be triggered

Upvotes: 0

user330315
user330315

Reputation:

You are mis-understanding what OLD and NEW are: those aren't tables, it is the row that has been inserted or modified.

However you do not need to delete and insert everything. Just increment the version in a BEFORE trigger.

Then you can simplify your trigger to:

create or replace function pmigratedatatohistory()
returns trigger as $$
BEGIN
  insert into organization_history values (old.*);
  new.version := new.version + 1; -- increment the version
  return new;
END;
$$ 
LANGUAGE plpgsql;

You need a BEFORE trigger for that:

create trigger trmigratedatatohistory BEFORE update or delete ON organization
    for each row execute procedure pmigratedatatohistory();

The process when you delete a row doesn't make sense to me. The current code does the following:

  1. A delete from organization is executed and the row is deleted
  2. The trigger makes a copy of that row
  3. The trigger tries to delete that row again (and does nothing, because it's an after trigger, so the row is already gone from the table)
  4. The rigger re-inserts the row that should have been deleted with a higher version number - essentially reverting the delete completely.

The same behaviour can be achieved by the simply incrementing the version in the before trigger. If you want to prevent deletion completely simply return null in the before trigger:

create or replace function pmigratedatatohistory()
returns trigger as $$
BEGIN
  insert into organization_history values (old.*);
  new.version := new.version + 1; -- increment the version
  if TG_OP = 'DELETE' then 
    return null; -- "cancel" the delete 
  else
    return new;
  endif;
END;
$$ 
LANGUAGE plpgsql;

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 246898

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.

Upvotes: 3

Related Questions