patternsqlModerate

Trigger: move deleted rows to archive table

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

rowstriggermovedeletedtablearchive

Problem

I have a small (~10 rows) table called restrictions in my PostgreSQL database, where values are deleted and inserted on a daily basis.

I would like to have a table called restrictions_deleted, where every row that is deleted from restrictions will be stored automatically. Since restrictions has a serial id, there will be no duplicates.

How do I write such a trigger in PostgreSQL?

Solution

You just need to move the old data into the restrictions_deleted table before it gets deleted. This is done with the OLD data type. You can use a regulat INSERT statement and and use the OLD values as the values-to-be-inserted.

CREATE TRIGGER moveDeleted
BEFORE DELETE ON restrictions 
FOR EACH ROW
EXECUTE PROCEDURE moveDeleted();

CREATE FUNCTION moveDeleted() RETURNS trigger AS $
    BEGIN
       INSERT INTO restrictions_deleted VALUES(OLD.column1, OLD.column2,...);
       RETURN OLD;
    END;
$ LANGUAGE plpgsql;

Code Snippets

CREATE TRIGGER moveDeleted
BEFORE DELETE ON restrictions 
FOR EACH ROW
EXECUTE PROCEDURE moveDeleted();


CREATE FUNCTION moveDeleted() RETURNS trigger AS $$
    BEGIN
       INSERT INTO restrictions_deleted VALUES(OLD.column1, OLD.column2,...);
       RETURN OLD;
    END;
$$ LANGUAGE plpgsql;

Context

StackExchange Database Administrators Q#2531, answer score: 19

Revisions (0)

No revisions yet.