CODEWITHSUNDEEP
mysqldatabase-triggersoft-delete
Amit
Amit

Reputation: 126

How to use trigger to prevent delete on table and update instead?

I would like to implement soft-delete in mysql using trigger and doing an update instead of delete. Then I found out that mysql doesn't have instead-of-Triggers.

So I would like to use a Before-Trigger but I couldn't find any way to abort the delete without throwing an error.

I'm looking for something like this:

use testdb;
DELIMITER $$
CREATE TRIGGER `table1_before_delete` BEFORE DELETE ON `table1`
FOR EACH ROW
BEGIN
    call abort_delete_and_dont_raise_error();
    UPDATE table1 SET deleted_at=NOW() where id=old.id;
END$$   
DELIMITER ;

I couldn't find anything like this.

Upvotes: 0

Views: 633

Answers (1)

Maksym Fedorov
Maksym Fedorov

Reputation: 6456

You can't abort the delete in Mysql without throwing an error. You shouldn't use the trigger of before delete to implement soft-delete mechanism. This is a wrong way. You should implement soft-delete mechanism in your application.

Upvotes: 1

Related Questions