MySQL requires a trigger to resolve the limit on the number of tables in the database

One requirement for recent projects is to limit the number of operations logs to 100,000, and more than 100,000 to delete the oldest one, saving no more than 100,000 of the logs in the database.
My first thought was to do it through a trigger and execute the following SQL in the database:

Delimiter $ Create trigger limitlogbeforeinsert on Operationlog for  each Row begin if (Select Count(*)  from Operationlog) > 100000  Then Delete from Operationlog limit 1;   End if; end $delimiter;

It does not seem to be a problem to perform a decision before insert, if the number exceeds 100000, the deletion is performed. But in the real database more than 100,000, that is, the start of the execution if statement when the problem, MySQL error:

update table ‘OperationLog‘ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Check the data to know that MySQL in order to prevent the trigger recursive loop execution, do not allow a table in the trigger directly to the table DML (Select,delete,update,insert) operations, of course, you can do this to other tables.
The trigger limits the DML operations that are performed on the table. The trigger can then modify the row of data to be executed before your execution, via the SET keyword.

delimiter $create trigger setLogbeforeinserton OperationLogfor each rowbeginset NEW.action = ‘test‘;end $delimiter ;

The above statement indicates that before the Insert Opetationlog table, the Action field value of the update insert data is test,new to represent the newly added field, and the same old represents the field at the time of the delete. Both new and old are available at the time of update.

Temporary triggers
The trigger (Triggers) You just talked about is triggered by an event generated by a table, and a temporary trigger is called an event scheduler that performs certain tasks based on a specific time period trigger. The MySQL event Scheduler can perform a task precisely every second, while the operating system's scheduled tasks, such as Cron under Linux or Scheduled tasks under Windows, can only be performed once per minute. For some applications that require a higher level of real-time data (e.g., stocks, odds, scores, etc.), it is very suitable.
Before you can use this feature, you must ensure that Event_scheduler is turned on and executable

 GLOBAL event_scheduler = 1;

Or

SET GLOBAL event_scheduler = ON;

To see if the event Scheduler is currently turned on, perform the following sql:

SHOW VARIABLES LIKE ‘event_scheduler‘;

Or

SELECT @@event_scheduler;

Or

SHOW PROCESSLIST;

For the problems mentioned at the beginning of this article, the use of this mechanism can be a perfect solution:

delimiter $CREATE EVENT limitLog ON SCHEDULE EVERY 1 SECOND DO IF (select count(*) from OperationLog) > 100000 then delete from OperationLog limit 1;END IF $ delimiter ;

Pro-Test effective

MySQL requires a trigger to resolve the limit on the number of tables in the database