Simulate a DELETE CASCADE in MySQL?

Question

Is it possible to predict the operations that follow a DELETE CASCADE automatically? In my software I would like to give the user a warning with details about the data that would be deleted then.

Answer 1

You can make a copy of the database and put triggers on the after delete

DELIMITER $$

CREATE TRIGGER ad_table1_each AFTER DELETE ON table1 FOR EACH ROW
BEGIN
  INSERT INTO log VALUES (null                 /*autoinc id*/
        , 'table1'                             /*tablename*/
        , old.id                               /*tableid*/
        , concat_ws(',',old.field1,old.field2  /*CSV's of fields*/
        , NOW()                                /*timestamp*/
        , 'delete');                           /*what action*/


  REPLACE INTO restore_table1 VALUES (old.id,
        , old.field1
        , old.field2
        , ... );

END $$

DELIMITER ;

The log table is just a table with the following fields:

id            integer autoincrement primary key
tablename     varchar(45)
table_id      integer
fields        varchar(6000)
delete_time   timestamp
action        enum('insert','update','delete')

If you do a SELECT @last_id:= max(id) FROM log before the delete cascade on the copy.
Then you can do a SELECT * FROM log WHERE id > @last_id
and get all the rows that will be deleted in the cascade.

After that you can use the restore_table1 to recreate the rows that were deleted in the cascade in the copy database.

Answer 2

I think you could use Johan's trigger solution in combination with a transaction that you roll back. This avoids both the need for a second database and for the manual restore of the deleted entries.

• add the trigger and the log table
• for each attempted deletion start a transaction and delete the entries
• present the information from the log to your user for approval
• if the user agrees commit the transaction, otherwise rollback