limit number of rows using a trigger

[Darren White]

I want a (log) table to grow either for a certain number of rows or for a certain number of days. Then when I reach that limit, delete the oldest row before inserting a new. As with a roatating buffer.

As a test I made the following table:
delimiter $$

CREATE TABLE `a_test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`a` int(11) NOT NULL DEFAULT '0',
`b` int(11) NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 MAX_ROWS=5$$

Then I made an attempt at the trigger


-- Trigger DDL Statements
DELIMITER $$
USE `test`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `test`.`log_limit`
BEFORE INSERT ON `pgs`.`a_test`
FOR EACH ROW

BEGIN

SELECT COUNT(*) FROM a_test;

IF COUNT >= 5 THEN
DELETE FROM a_test ORDER BY last_update LIMIT 1;
END IF;

END$$


But I get this error:
SQL Error 1415: Not allowed to return a result set from a trigger



Edited 1 time(s). Last edit at 03/13/2012 03:55AM by Darren White.