sql delete from start

Question

Is there anyway I can restrict the amount of rows allowed in a table to say 40, and then when 41st is added, the table deletes the first one?

Answer 1

Yes, you could do this with a trigger. What RDMS are you using?

Answer 2

CREATE TABLE animals (
 id MEDIUMINT NOT NULL AUTO_INCREMENT,
 name CHAR(30) NOT NULL,
 PRIMARY KEY (id)
);

-- if this is 41st record, ...
-- the statement below will delete the id with 1, and so forth
insert into animals(name) values('wolverine'); 
delete from animals where id <=  LAST_INSERT_ID() - 40;