Save as INSERTs before delete records from multiple tables postgreSQL

1276 views php
5

I need an idea how to achieve something like a safe backup of data before deletion takes place. I have to delete different data from more than 20 tables(usually one record from each table) and then to be able to run the resulted "file" to restore everything.

answered question

2 Answers

2

You should google such thing as TRANSACTION which allows you to make atomic chain of SQL queries.

posted this
5

Perhaps have a _deleted_users table (and others) and use a procedure to move the rows like so (not tested code):

DELIMITER $$
CREATE PROCEDURE safeDelete(
    IN id_in INT
) BEGIN
    INSERT INTO _deleted_users (<columns>)
    VALUES (SELECT * FROM _users WHERE id = id_in);
    DELETE FROM _users WHERE id = id_in;
END $$
DELIMITER ;

You could then just call it like so:

CALL safeDelete(1); /* where 1 is their ID (Primary Key) */

Then another restore() procedure to move them all back.

posted this

Have an answer?

JD

Please login first before posting an answer.