If a row is deleted, trigger inserting it into another table

Question

I'm trying to create a trigger that copies a deleted row to another table. So far when I delete a row I just copy the entire first table into the second table which isn't very useful.

Table 1 is comments with comment_id, file_id, user_id, comment_text, comment_datetime and parent

Table 2 is comment_log, which contains deleted_comment_id, file_id, user_id, comment_text, comment_datetime and comment_deletion_datetime.

So I only want to store comments deleted by users, moderators or administrators 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 what I have so far, I've tried things like a WHERE behind it but I don't know where to put it. old.comment_id should give me the old id, but I don't know how to get the comment with that id from the comments table.

Answer 1

The column of the deleted row is available in the trigger as OLD.*, so I would do this:

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;