Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 11 down vote accepted

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;
share|improve this answer

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 :)

share|improve this answer
    
+1 Thanks - wouldn't work for my case, but it's a nice approach that uses a single table only. –  Adam Matan May 5 '11 at 10:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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