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.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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;