我想要一个触发器从 MySQL 中的 2 个表中删除

2022-01-01 00:00:00 mysql sql-delete triggers

我有 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.

相关文章