I want to log deleted data from certain tables. That from logs I could recover the deleted data. Mysql supports something similar out of the box? Ideally, get a sql file with "insert".
1 Answer
There is a partial solution. For every table you want to audit, create BEFORE DELETE trigger and an audit table. See code below.
Keep in mind that triggers work only for DELETE FROM command. TRUNCATE bypass triggers and your deletes will not be audited.
DELIMITER //
CREATE TRIGGER table1_before_delete
BEFORE DELETE
ON table1 FOR EACH ROW
BEGIN
-- Insert record into audit table
INSERT INTO table1_audit ( table1_id, deleted_date) VALUES ( OLD.id, NOW());
END; //
DELIMITER ;
