MySQL BEFORE DELETE INSERT

Question

So I'm trying to insert information into a table upon deletion in another table, but I want to gather information also from other tables again which will also be deleted under a cascade. I mean best way to explain it is:

DELIMITER $$

CREATE TRIGGER before_student_delete
BEFORE DELETE
ON students FOR EACH ROW
BEGIN
    INSERT INTO student_deletion(student_ID, course_code, department_code)
    VALUES(OLD.student_ID,select course_code
                                 from enrolled
                                 where student_ID = OLD.student_ID,
                                 select department_code
                                 from course_dept_key
                                 where course_code = select course_code
                                 from enrolled
                                 where student_ID = OLD.student_ID);
END$$

You know I'm trying to use the old information to find that info in other tables, but...

I'm totally new at this, thanks for any help.

Answer 1

Use for the Selects session variables and insert them into the table.

Like

DELIMITER $$

CREATE TRIGGER before_student_delete
BEFORE DELETE
ON students FOR EACH ROW
BEGIN
    SELECT course_code INTO @course_code
     FROM enrolled
     WHERE student_ID = OLD.student_ID;
    SELECT department_code INTO @department_code
     FROM course_dept_key
     WHERE course_code = @course_code;

    INSERT INTO student_deletion(student_ID, course_code, department_code)
    VALUES(OLD.student_ID,@course_code,@department_code);
END$$
DELIMITER ;