CODEWITHSUNDEEP
postgresql
eilchner
eilchner

Reputation: 131

Trigger inserting deleted rows in Postgresql

Let's say I've got two tables, a and b, both have two rows int and date. I want to write a trigger that, when deleting rows from table a, copies them to table b with the date changed to the current date. So, if I delete a row with 13, 2015-01-01 today from table a, 13, 2015-06-08 gets inserted into table b. Any ideas on how to get this done?

Upvotes: 1

Views: 2655

Answers (1)

Gregor Raýman
Gregor Raýman

Reputation: 3081

create or replace function a_adr_tf() returns trigger as $$
begin
  insert into b(aid, awhen) values (old.id, now());
  return old;
end;
$$ language plpgsql;

create trigger a_adr after delete from a for each row execute procedure a_adr_tf();

Note that this trigger does not take care of possible duplicate primary key in the table b. I have assumed the columns in A are called id and when and in B they are called aid and awhen.

You can solve the problem of the primary key of B by having a PK in B of the type serial, or, if you want just one row in B with the same id from A with a trigger deleting from B when a new row is inserted into A.

Upvotes: 4

Related Questions