Trigger when tresshold is passed (MySQL)

I have a table with Serial(primary key), text, date

It get's inserted every so often with a row.

The problem I have is that my table may not contain less than 80 rows and no more than 100.

I tried writing something, but it seems I am doing stuff I shouldn't be doing :

CREATE TRIGGER checkForInsertBelow80 BEFORE INSERT ON log FOR EACH ROW 
BEGIN
  IF (SELECT count(*) FROM log) >= 100 THEN
    DELETE FROM log 
      WHERE serial in(SELECT serial from log order by serial ASC limit 10);
  END IF      
END; |

But first of all I have syntax errors, second of all, I should be doing this after the last row.

Can someone help me to put the right approach and code together 开发者_Python百科?

---

1- Every statement in a stored proc must be terminated with a ;.

2- You can simplify the delete statement.

3- However you cannot change the same table inside a trigger, you can only change other tables inside a trigger.

Solution

Use a blackhole table and insert into that, the blackhole inserts your data into the log table, or deletes log entries if needed.

CREATE TABLE bh_log (
  id integer null default null,
  field1 varchar(255) not null,
  other fields....
) ENGINE = BLACKHOLE;

Now add triggers to the blackhole table as needed.

You need a INSERT trigger, UPDATES and DELETES are done on the original log table.

DELIMITER $$

CREATE TRIGGER ai_bh_log_each AFTER INSERT ON bh_log FOR EACH ROW
BEGIN
  DECLARE log_count INTEGER;
  INSERT INTO log VALUES (NEW.id, NEW.field1, NEW......);
  SELECT count(*) INTO log_count FROM log;
  IF log_count > 100 THEN 
    //Delete the oldest entry
    DELETE FROM log WHERE log.id IS NOT NULL ORDER BY id ASC LIMIT 1; 
  END IF;
END $$

DELIMITER ;

Because the trigger fires for every row that you insert, you only need to delete 1 row in each trigger to be 100% that the total number of rows never exceeds a 100.