在做数据库关于触发器的实验的时候，突发奇想，如果触发器的触发动作体里含有外键的话，对触发器有什么影响：
通过下面的实验来学习一下：
- 触发器删除不含外键的表
DROP TABLE if EXISTS a; DROP TABLE IF EXISTS b; CREATE TABLE a( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); CREATE TABLE b( id INT PRIMARY KEY AUTO_INCREMENT, pwd VARCHAR(20) ); DELIMITER $; CREATE TRIGGER TEST_A AFTER INSERT ON a FOR EACH ROW BEGIN INSERT INTO b(pwd) VALUES(NEW.NAME) ; END $; DELIMITER ; DELIMITER $; CREATE TRIGGER TEST_B AFTER DELETE ON a FOR EACH ROW BEGIN DELETE FROM b WHERE pwd = old.NAME ; END $; DELIMITER ;
测试样例
INSERT INTO a(NAME) values('hello1'); select * from a; select * from b; delete from a where NAME = 'hello'; select * from a; select * from b;
- 给b添加外键
DROP TABLE IF EXISTS a; DROP TABLE IF EXISTS b; CREATE TABLE a( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); CREATE TABLE b( id INT PRIMARY KEY AUTO_INCREMENT, pwd VARCHAR(20), fk INT NOT NULL, FOREIGN KEY(fk) REFERENCES a(id) ); DELIMITER $; CREATE TRIGGER TEST_A AFTER INSERT ON a FOR EACH ROW BEGIN INSERT INTO b(pwd,fk) VALUES(NEW.NAME, NEW.id) ; END $; DELIMITER ; DELIMITER $; CREATE TRIGGER TEST_B AFTER DELETE ON a FOR EACH ROW BEGIN DELETE FROM b WHERE pwd = old.NAME ; END $; DELIMITER ; DELIMITER $; CREATE TRIGGER TEST_C AFTER update ON a FOR EACH ROW BEGIN UPDATE b SET pwd = new.NAME WHERE pwd = old.NAME ; END $; DELIMITER ;
测试
INSERT INTO a(NAME) values('hello'); select * from a; select * from b; delete from a where NAME = 'hello1'; select * from a; select * from b; update a set NAME = 'new name' WHERE NAME = 'HELLO'; select * from a; select * from b;
结果：有外键的删除就会出问题。
SQL 查询： delete from a where NAME = 'hello1' MySQL 返回： 文档 #1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mydata`.`b`, CONSTRAINT `b_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `a` (`id`))
总结
在触发器里面如果修改的表有外键的话，删除的时候就会出问题。没有外键则随便删除。增加，查询，更新都没有影响。
![[MySQL]知识点](https://ucc.alicdn.com/3mfxe2r26qbaq/developer-article1634557/20241106/780ea8401c02437e9a7f560ce6cf77e9.jpeg?x-oss-process=image/resize,h_160,m_lfit)