MySQL DELETE触发器

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO archive_orders(order_num, order_date, cust_id)
  VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_orders的存档表中

--《MySQL必知必会》

然而执行此触发器会报如下错误

Cannot delete or update a parent row: a foreign key constraint fails (`crashcourse`.`orderitems`, CONSTRAINT `fk_orderitems_orders` FOREIGN KEY (`order_num`) REFERENCES `orders` (`order_num`)) 

看报错提示显示，orders的order_num是orderitems表的外键。可以在DELETE触发器中加入对orderitems的删除操作解决这个问题。

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
DELETE FROM orderitems where order_num=OLD.order_num;
INSERT INTO archive_orders(order_num, order_date, cust_id)
VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

维护引用完整性

 orders表和orderitems表通过主键和外键组成关系表，如果order表的行可以直接删除，那么被删除的order_num在orderitems表中对应的订单物品没法再通过联结来访问，同时这些行的order_num也会变成非法值。MySQL通过此机制来维护引用完整性。

锚点：MySQL必知必会DELETE触发器执行报错

最后

以上就是风中星月为你收集整理的MySQL DELETE触发器的全部内容，希望文章能够帮你解决MySQL DELETE触发器所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。