CODEWITHSUNDEEP
mysql
RJ501
RJ501

Reputation: 167

How to limit total no of rows in mysql table

I want a table maximum rows to 100, If i add 101 row to that table, the 1 row should delete automatically. likewise i just want table total row count to 100, delete order should be FIFO.
Is there any direct MySQL function for that?

Upvotes: 4

Views: 246

Answers (2)

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

You can a simple trigger like below:

CREATE TRIGGER Deleter AFTER INSERT on YourTable FOR EACH ROW
BEGIN
    Delete from yourTable where ID = (Select MIN(id) from yourTable);
END;

Same as : How do you make a threshold or limit mysql table?

Upvotes: 1

Jayesh
Jayesh

Reputation: 6111

Use BEFORE INSERT trigger for this.

Below, I kept limit of 25, set according to your need.

DELIMITER $$

CREATE TRIGGER trigger1
BEFORE INSERT
ON table1
FOR EACH ROW
BEGIN
  SELECT COUNT(*) INTO @cnt FROM table1;
  IF @cnt >= 25 THEN
    CALL sth(); -- raise an error
  END IF;
END
$$

DELIMITER ;

Upvotes: 5

Related Questions