我想要从MySQL 2个表中删除的触发器 [英] I want a trigger to DELETE from 2 tables in MySQL

问题描述

我有3个MySQL表(food，apple和orange).

I have 3 MySQL tables (food, apple, and orange).

我要删除以下行:

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.

这篇关于我想要从MySQL 2个表中删除的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！