mysql triggers on delete a raw

Question

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;
$$

Instead of "AFTER DELETE" , Write "BEFORE DELETE" and let us know — Alpesh Prajapati
– Alpesh Prajapati, Commented Jun 14, 2012 at 6:35

Joe · Accepted Answer · 2012-06-14 06:25:52Z

2

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.

answered Jun 14, 2012 at 6:25

Joe

2,6136 gold badges40 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql triggers on delete a raw

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related