I'm currently creating archive records for my database, where each table x has a corresponding x_archive table. My solution was to create a trigger for each table that needed replication that would insert the deleted data into the archive table. The trigger seems to run just fine, with the data being replicated to the archive table. The original data, however, is not deleted. The trigger only seems to run on the first delete query. If I run the same delete statement again (on the original data), both the row in the original table and the archive table is deleted. I've also tried to create trigger functions that are specifically tailored to inserting into a specific table, but the same result occurs. Any help is greatly appreciated.
CREATE OR REPLACE FUNCTION archive_record() RETURNS TRIGGER AS $$ BEGIN RAISE NOTICE 'Running trigger'; EXECUTE format('INSERT INTO %I.%I SELECT $1.*', TG_TABLE_SCHEMA, (TG_TABLE_NAME || '_archive')) USING OLD; RETURN NULL; END; $$ LANGUAGE PLPGSQL;
CREATE TRIGGER delete_test AFTER DELETE ON test FOR EACH ROW EXECUTE PROCEDURE archive_record();
create table test ( id serial primary key, name varchar(128) not null, );
Example archive table
CREATE TABLE test_archive ( ) INHERITS(test);