Trigger to insert row into another table if deleted-CodePudding

I'm trying to create a trigger that will duplicate the ONE row that was deleted to another table. So far, I have just duplicated the whole first table into the second one when I deleted one row, not very useful.

Table1 is comment with comment_id, file_id, user_id, comment_text, comment_datetime and parent

Table2 is comment_log with deleted_comment_id, file_id, user_id, comment_text, comment_datetime and comment_deletion_datetime.

So I just want comments that have been deleted by a user, moderator or admin to be stored in comment_log.

INSERT INTO comment_log(deleted_comment_id, file_id, user_id, comment_text,comment_datetime, comment_deletion_datetime)
SELECT comment.comment_id, file_id, user_id, comment_text, comment_datetime, CURRENT_TIMESTAMP
FROM comment

This is where I've gotten so far, I have tried stuff like a WHERE behind, but I have no idea what to put there. old.comment_id should give me the old id, but I have no idea how to get just the comment with that id from the comment table.

CodePudding user response：

The columns of the row that is deleted are available in the trigger as OLD.*, so I'd do it this way:

INSERT INTO comment_log
SET deleted_comment_id = OLD.comment_id, 
    file_id = OLD.file_id, 
    user_id = OLD.user_id,
    comment_text = OLD.comment_text,
    comment_datetime = OLD.comment_datetime, 
    comment_deletion_datetime = CURRENT_TIMESTAMP;