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?
-
What have you tried? What you describe doesn't sound difficult, so I am unclear on what exactly is giving you trouble.Politank-Z– Politank-Z2015-06-08 18:50:06 +00:00Commented Jun 8, 2015 at 18:50
-
I have a problem with the function. I don't know if deleted rows are stored somewhere, that I can copy them from. I've thought of making a temporary table in which I would change the date, but that's it.eilchner– eilchner2015-06-08 18:53:59 +00:00Commented Jun 8, 2015 at 18:53
-
Post the code of your function.Luc M– Luc M2015-06-08 18:58:10 +00:00Commented Jun 8, 2015 at 18:58
-
For a general solution of an audit trigger, see the Postgres Wiki: wiki.postgresql.org/wiki/Audit_trigger_91plususer330315– user3303152015-06-08 20:16:15 +00:00Commented Jun 8, 2015 at 20:16
Add a comment
|
1 Answer
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.
1 Comment
eilchner
Thanks a lot, the only thing I didn't know was the
old.id part.