I have two tables. Here are the tables and their fields.
articles
id
title
comments
id
content
article_id
The relationship is one to many (article 1<--->* comment)
When I delete an article, I want all the article's comments to also be deleted.
What is the Trigger sql to do this?
If you're using InnoDB engine all you need to do is to define a FK constraint with ON DELETE CASCADE
Proposed schema might look like this
CREATE TABLE articles
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(256)
);
CREATE TABLE comments
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
content VARCHAR(10),
article_id INT,
FOREIGN KEY (article_id)
REFERENCES articles(id) ON DELETE CASCADE
);
Then when you delete a row from articles
DELETE FROM articles WHERE id = 1;
all corresponding rows from comments will be deleted automatically.
Here is SQLFiddle demo
Now if you're using MyISAM engine which doesn't have implementation for FKs or you for some reason want to enforce it via trigger use AFTER DELETE event like this
CREATE TRIGGER tg_ad_articles
AFTER DELETE ON articles
FOR EACH ROW
DELETE FROM comments
WHERE article_id = OLD.id;
Note: It's a one statement trigger so you don't need to change DELIMITER and use BEGIN...END block.
Here is SQLFiddle demo
This is not tested. (Change delimiter to add to db).
CREATE TRIGGER `article_before_delete`
BEFORE DELETE ON `articles`
FOR EACH ROW
BEGIN
DELETE FROM `comments` where `article_id` = OLD.id;
END
Similar question answered here I want a trigger to DELETE from 2 tables in MySQL
