24

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?

2 Answers 2

19

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;
2
  • 2
    You can replace the explicit list of column values with something like VALUES((OLD).*)
    – KayEss
    Commented Apr 16, 2015 at 4:26
  • 2
    Works fine, but the create function needs to be called before create trigger. And the VALUES((OLD).*) trick suggested by KayEss is nice.
    – mivk
    Commented Apr 2, 2016 at 14:14
8

If you are open to a different approach, Have you considered adding a 'deleted' Boolean flag to the table, or a 'deleted_at' timestamp instead.

Or better still, deny CRUD access to your database tables and handle the audit trail in your transactional API :)

1
  • This will cause performance issues when deleted record count is too large (millions)
    – echo
    Commented May 2, 2022 at 2:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.