mysql - 用于记录回收的 SQL 触发器

Question

我创建了一个 MySQL 触发器，旨在每次添加新记录时清除最旧的记录:

CREATE DEFINER =  `root`@`localhost` TRIGGER `cleanRecords` BEFORE INSERT ON  `Records` 
FOR EACH
ROW BEGIN 
SELECT COUNT( * ) 
INTO @cnt 
FROM cla.Records;

IF @cnt >=1000 THEN DELETE FROM cla.Records ORDER BY createdDate DESC LIMIT 1 ;

END IF ;

END

期望在连续操作中保留 1000 条最新记录。该操作已确认有效，但此触发器无效。是我误解了触发器还是这个触发器写得不正确？

Answer 1

来自here :

A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

因此您无法删除激活触发器的表中的行。

您可能应该在应用程序中处理这个问题，或者使用不时清理表格的进程