mysql triggers on delete a raw
I have two tables as students and certificates, I need to delete the certificates related to a particular student from the certificates table when deleting a student from the student table. I am not much familiar with mysql triggers. please assist me.
this is what I've tried but I not sure it is correct
DELIMITER $$ CREATE TRIGGER delCerts AFTER DELETE ON students FOR EACH ROW BEGIN DELETE FROM certificates as certs WHERE certs.stCode = students.stCode; END; $$
In InnoDB databases you could achieve it by having foreign key in certificates table:
idStudent INT NOT NULL, FOREIGN KEY (idStudent) REFERENCES Students (id) ON DELETE CASCADE ON UPDATE CASCADE
When you delete student from stuends table - all related certificates will be also deleted. I think it's better than trigger, because if you or someone will disable triggers (accidentally or on purpose) your db will be inconsistent.