MySQL 触发器是否会触发其他触发器？

Question

例如，如果我定义了一个触发器，在删除帖子时删除该帖子的所有评论。那么当删除用户时，是否会激活删除所有帖子的触发器，从而删除评论？

- Liam Potter

Answer 1

简短的回答是是的。

演示：

mysql> create table users (id serial primary key);

mysql> create table posts (id serial primary key, userid bigint unsigned);

mysql> create table comments (id serial primary key, postid bigint unsigned);

mysql> create trigger udel after delete on users for each row delete from posts where userid = OLD.id;

mysql> create trigger pdel after delete on posts for each row delete from comments where postid = OLD.id;

mysql> insert into users values (123);
Query OK, 1 row affected (0.00 sec)

mysql> insert into posts values (456, 123);
Query OK, 1 row affected (0.00 sec)

mysql> insert into comments values (789, 456);
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+-----+
| id  |
+-----+
| 123 |
+-----+
1 row in set (0.00 sec)

mysql> select * from posts;
+-----+--------+
| id  | userid |
+-----+--------+
| 456 |    123 |
+-----+--------+
1 row in set (0.00 sec)

mysql> select * from comments;
+-----+--------+
| id  | postid |
+-----+--------+
| 789 |    456 |
+-----+--------+

现在我们尝试进行删除，这将会级联到其他表中：

mysql> delete from users;
Query OK, 1 row affected (0.03 sec)

mysql> select * from posts;
Empty set (0.00 sec)

mysql> select * from comments;
Empty set (0.00 sec)

- Bill Karwin