CODEWITHSUNDEEP
sqldatabasetriggersmariadb
user2285265
user2285265

Reputation: 123

Trigger ON DELETE using MariaDB

I have two tables 'topic' and 'answer':

Table 1-> topic

+-----+------------+
+  id +   name     +
+=====+============+
+  17 +  question1 +
+-----+------------+
+  18 +  question2 +
+-----+------------+

Table 2 -> answer

+----+---------+----------+
+ id + idtopic +   val    +
+----+---------+----------+
+  1 +  17     +  anwer1  +
+----+---------+----------+
+  2 +  18     +  answer2 +
+----+---------+----------+

I'm simply trying to create a trigger to delete items from table 'answer', when deleting items from table 'topic'. If tried this code, but there is problem with MariaDB syntax I think:

CREATE TRIGGER delete_answer AFTER DELETE on topic
FOR EACH ROW
BEGIN
DELETE FROM answer
    WHERE answer.idtopic = topic.id;
END

Upvotes: 0

Views: 2958

Answers (2)

SlackO
SlackO

Reputation: 51

Here is the version that will work.

DELIMITER //
CREATE TRIGGER delete_answer AFTER DELETE on topic
FOR EACH ROW
BEGIN
DELETE FROM answer
    WHERE answer.idtopic = OLD.id;
END
//
DELIMITER ;

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562811

Use the OLD keyword to refer to the current row in topic that is being deleted.

CREATE TRIGGER delete_answer AFTER DELETE on topic
FOR EACH ROW
BEGIN
DELETE FROM answer
    WHERE answer.idtopic = OLD.id;
END

However, I wonder why you're writing a trigger instead of using a foreign key with cascading delete. Are you not using InnoDB for this table?

Upvotes: 0

Related Questions