我想要一个触发器从 MySQL 中的 2 个表中删除
我有 3 个 MySQL 表(food
、apple
和 orange
).
I have 3 MySQL tables (food
, apple
, and orange
).
我想从以下位置删除行:
I want to delete rows from:
apple(idapple, iduser, name)
orange(idornge, iduser, name)
当使用一个触发器删除food(iduser, name)
中的一行时?
When deleting a row in food(iduser, name)
using one trigger?
到目前为止,这是我的触发器:
Here is my trigger so far:
CREATE TRIGGER `food_before_delete`
AFTER DELETE ON `food`
FOR EACH ROW
DELETE FROM apple, orange
WHERE
apple.iduser=OLD.iduser and
orange.iduser=OLD.iduser
但它不会编译.如何制作一个同时从两个表中删除的触发器?
But it won't compile. How can make a trigger that deletes from two tables at once?
推荐答案
使用触发器一次从两个表中删除:
触发器用于强制执行表中的数据完整性.您可以使用触发器一次从任意数量的表中删除.
Triggers are used to enforce data integrity in the tables. You can use triggers to delete from any number of tables at once.
在初始化触发器之前,我们需要临时更改mysql分隔符运算符,因为触发器使用分号;
运算符来指定触发器内的多个sql命令.
Before initializing triggers we need to change the mysql delimiter operator temporarily because triggers use semicolon ;
operator to specify multiple sql commands within the trigger.
第 1 步更改当前分隔符:
delimiter $$
第 2 步创建触发器:
CREATE TRIGGER `blog_before_delete`
AFTER DELETE ON `blog`
FOR EACH ROW
BEGIN
DELETE FROM blog_tags where blogid = OLD.id;
DELETE FROM blog_comments where blogid = OLD.id;
END
$$
第 3 步恢复上一个分隔符:
delimiter ;
说明:
OLD
是一个内置关键字,指的是我们要删除的博客表行.每当我们删除博客表中的条目时,Mysql 都会运行触发器 blog_before_delete
.我触发器失败,然后删除被回滚.这有助于确保我们数据库中的原子性、一致性、隔离性和持久性.
OLD
is a builtin keyword and refers to the blog table row that we are deleting. Mysql runs the trigger blog_before_delete
whenever we delete an entry in the blog table. I the trigger fails, then the delete is rolled back. This helps ensure Atomicity, Consistency, Isolation, and Durability in our database.
相关文章