CODEWITHSUNDEEP
sqlpostgresqlplpgsqlsql-deletedatabase-trigger
user2859458
user2859458

Reputation: 2495

Postgres triggers: write old record to new table

I have a set of different tables that have a trigger on DELETE. I'd like to write deleted rows to a like table on a different schema.

For example, say I have a couple different tables:

CREATE TABLE A (val1 TEXT, val2 TEXT)
CREATE TABLE B (someval1 INT, someval2 TEXT, someval3 TEXT)

And I have copies of these same tables on some other schema

CREATE SCHEMA backup;
CREATE TABLE backup.A (like A);
CREATE TABLE backup.B (like B);

Each non-backup table A & B has a trigger for deletions (written in plpgsql). If I delete a record from either of these tables, how can I write a trigger function that writes the deleted record to the associated backup table?

Upvotes: 2

Views: 1102

Answers (2)

user2859458
user2859458

Reputation: 2495

I was able to write the deleted record to my backup tables using a single, non-table specific trigger function:

create or replace function track_delete() returns trigger as
$body$
BEGIN
    execute format('insert into backup.%I values($1.*)', tg_table_name) USING old;
END;
$body$
language plpgsql;

Upvotes: 3

GMB
GMB

Reputation: 222432

You can do that with a trigger indeed. You need one trigger per table, like:

create or replace function func_backup_tablea_before_delete()
return trigger as
$body$
begin
    insert into backup.tablea(val1, val2) values (old.val1, old.val2);
end;
$body$ 

create trigger trg_delete_a 
before delete on tablea
for each row
execute procedure func_backup_tablea_before_delete;

Upvotes: 1

Related Questions