MySQL triggers: move to trash

Question

I want to create a trigger in MySQL that will do two things: if forum's topic is located in trash or it is hidden, delete it, elsewhere move the topic to trash. The question is how to stop the delete action in 'before delete' trigger?

Answer 1

I don't know if there's a way to prevent the delete once it has already been called without raising an exception of some sort.

I think a better solution might be instead of calling delete on the record you want to trash/delete you should update a field such as "IsTrashed" to TRUE. And then in the update trigger, see if it was already TRUE and being set to TRUE again (e.g. IF(OLD.IsTrashed && NEW.IsTrashed)). If so, delete it, otherwise move it to the trash.

The only problem that will arise from this method is if you update a different field (e.g. PostDate) of a trashed item, NEW.IsTrashed and OLD.IsTrashed will both be TRUE so it might look like you are trying to delete it, but you are only updating the PostDate. You can either check that this is the only field that was modified (e.g. by checking OLD.SomeField <> NEW.SomeField for every other field) or use a field that will always reset it's value to NULL after an UPDATE statement. Something like "TrashNow". That way if TrashNow ever has a TRUE value, you know that you did intentionally want to trash the field.

However, that "reseting field" is just wasted space, I think the best solution for this problem is a stored procedure... something like:

CREATE PROCEDURE DeletePost (IN APostID INT)
BEGIN
  IF ((SELECT InTrash FROM posts WHERE PostID = APostID LIMIT 1))
    DELETE FROM posts WHERE PostID = APostID;
  ELSE IF
    UPDATE posts SET InTrash = TRUE WHERE PostID = APostID;
  END IF;
END;

Assuming you have the table posts with the fields PostID (INT) and InTrash (any integer type).

You would call this like so to delete post with PostID 123:

CALL DeletePost(123);