CODEWITHSUNDEEP
mysqlsql
Prostitutor
Prostitutor

Reputation: 383

mysql stored procedure glitching

I have to limit table record to 25. after I just delete everything (in the future will modify it to delete just oldest rows)

DELIMITER //
CREATE PROCEDURE p1 () delete FROM tests;//

CREATE TRIGGER trigger1
BEFORE INSERT
ON tests
FOR EACH ROW
BEGIN
  SELECT COUNT(*) INTO @cnt FROM tests;
  IF @cnt >= 25 THEN
    CALL p1();
  END IF;
END
//

DELIMITER ;

, but I am getting error: Can't update table 'tests' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

So I can not add any more fields.

Upvotes: 0

Views: 61

Answers (2)

tzunghaor
tzunghaor

Reputation: 1035

The MySQL trigger FAQ sais that you cannot modify the table that calls the trigger.

But you can set up a cron job, or CREATE EVENT in MySQL that cleans the table at regular intervals. (CREATE EVENT needs the PROCESS privilege, and a running event_scheduler. The event_scheduler is turned off by default: it can be turned on from SQL console, but the MySQL config must be modified to ensure that it starts when MySQL restarts.)

Upvotes: 1

Tin Tran
Tin Tran

Reputation: 6202

It seems that you can't update or delete from the same table which invoked the trigger.
But you can work around this by creating another table, for example tests2 and instead of inserting into tests just insert into tests2, and have the trigger insert the NEW. values into tests, then you can count from tests and delete from tests like how you want it.
see this sqlFiddle
The problem with this is then tests2 gets filled up with Inserted data So you might have to manually delete from tests2.

Upvotes: 0

Related Questions