copy rows from one table to another triggered by insert on a 3rd table

Question

In my database there are four tables: task, tasknotes, task_archive and tasknotes_archive. When an entry is copied from the task table to the task_archive table I want to use a trigger to perform the following:

1. copy the related task notes from the tasknotes table to the tasknotes_archive table.
2. delete the entry in the task table I just copied to task_archive
3. delete the entries from tasknotes that I just copied to tasknotes_archive

The application that interfaces with the database is built in Java using JDBC. I could achieve the above results as either a series of calls to the database or as a transaction. However it would seem more efficient to have the initial insert statement than copies the row from task to task_archive trigger the rest of the events. I initially tested this by seeing if I could get a trigger to delete the entry from the task table based on the insert into task_archive. This seemed to work fine. However when I started trying to add in the script to cause the DB to copy from tasknotes to tasknotes_archive I got error messages stating that it doesn't recognise task_archive.task_id in the first where clause. Importantly, tasknotes and tasknotes_archive have the exact same table structure so this insert method should be possible as discussed in the answer to this question: MYSQL: How to copy an entire row from one table to another in mysql with the second table having one extra column?. I then tried changing this to new.task_id based on answers to other questions on stack. Still got error messages. The following code is the insert trigger contained in task_archive, which should I'm trying to develop to perform the above actions on tasknotes_archive and task:

CREATE
TRIGGER `myDB`.`task_archive_AFTER_INSERT`
AFTER INSERT ON `myDB`.`task_archive`
FOR EACH ROW
BEGIN
INSERT INTO tasknotes_archive
SELECT tasknotes.* FROM tasknotes
WHERE tasknotes.task_id = task_archive.task_id;

DELETE FROM task
USING task, task_archive
WHERE task.task_id = task_archive.task_id;
END

My question is, is it possible to have multiple events run as a trigger as described? Am I correct in assuming this is a more efficient way of performing this rather than multiple calls to the DB in java? Finally, what is the correct way to write this trigger?

Answer 1

You need to use NEW.task_id to get the task related to the current row of the trigger.

And if you're doing this using a CLI, you need the DELIMITER statement so you can have ; between the statements in the trigger.

DELIMITER $$

CREATE
TRIGGER `myDB`.`task_archive_AFTER_INSERT`
AFTER INSERT ON `myDB`.`task_archive`
FOR EACH ROW
BEGIN
    INSERT INTO tasknotes_archive
    SELECT tasknotes.* FROM tasknotes
    WHERE tasknotes.task_id = NEW.task_id;

    DELETE task, tasknotes
    FROM task JOIN tasknotes USING (task_id)
    WHERE task.task_id = NEW.task_id;
END
$$
DELIMITER ;