mysql - 为什么我的删除触发器没有删除任何行？

Question

这是我的触发代码:

DELIMITER $$
USE `smartclass_dbv2`$$ 
CREATE TRIGGER `delete_attendance_on_holiday` 
AFTER INSERT ON `attendance_tbl` 
FOR EACH ROW 
BEGIN 
     DELETE FROM `attendance_tbl` 
     WHERE (date = announcement_tbl.announcement_date && announcement_tbl.announcement_description = 'holiday'); 
END$$

它没有错误，但它不会删除任何行，我的代码意味着如果它在出勤表中发现任何日期与公告表中的日期相同并且是假期，它将删除出勤表中的行表。

这是我的出勤表:

http://prntscr.com/j22rme

这是我的公告表:

http://prntscr.com/j22rph

Answer 1

You cannot change a table while the INSERT trigger is firing. The INSERT might do some locking which could result in a deadlock. Also, DELETING or UPDATING the table from a trigger would then cause the same trigger to fire again in an infinite recursive loop. Both of these reasons are why MySQL prevents you from doing this. Documentation

但是您可以单独运行此查询。

您的删除查询不正确，请检查以下内容

DELETE T1
FROM `attendance_tbl` T1
LEFT JOIN announcement_tbl T2 ON T1.date= T2.announcement_date
WHERE announcement_tbl.announcement_description = 'holiday';

<强> Syntax Delete Using Joins

DELETE T1 
FROM T1
        LEFT JOIN
    T2 ON T1.key = T2.key 
WHERE
T2.key IS NULL;