how to create a fixed size table in mysql

[PreethiGowri]

I'm yet a learner so, i would be glad to everyone for their contribution in me gaining knowledge:)

I would like to known if we can create a fixed size table in mysql, if yes, then how do we do it?

[Rabbit]

What do you mean by fixed size table?

[PreethiGowri]

example - i want just 100 entries to be made into it, and i want the table to work in the form of FIFO when i make an 101 th entry

[Rabbit]

You can use an insert trigger to run a count and delete operation.

[PreethiGowri]

i'm new to that can you guide me please

[Rabbit]

Here is MySQL's documentation on triggers: http://dev.mysql.com/doc/refman/5.0/en/triggers.html

[PreethiGowri]

how do we put a count for insert in trigger query?

[Rabbit]

It's just a simple select count all query.

[PreethiGowri]

DELIMITER $$ 
 create  TRIGGER MyTrigger before insert on purchased
 delete from purchased where count = 3
 $$
 DELIMITER;

the count(*) value is aliased to count in my above query.
This query isn't working for me, can you correct this please:(

[PreethiGowri]

Finally done with this home work of trigger:):)

"create  TRIGGER MyTrigger before insert on purchased
 FOR EACH ROW 
 BEGIN 
 if count  > 3 then 
 delete from purchased
 insert into purchased values(id, name , quantity);
 END if;
 END ;

Thanks for your guidance Rabbit:):)

[PreethiGowri]

do we have an option of if else in trigger query?

[Rabbit]

Yes, you can use if else in a trigger.

[PreethiGowri]

i cant delete a row from the table before inserting new records to the same table. its showing " cant update trigger/function because it is already being used by trigger/function"...
help?

[Rabbit]

Please post your most current code.

[PreethiGowri]

create  TRIGGER MyTrigger before insert on purchased
FOR EACH ROW
BEGIN
if count  > 6 then
delete from purchased limit 1
elsif count <= 6 then
insert into purchased (RFID_NO, name, price, quantity, totalPrice, purchase_date, expiry,customer_name,ph_no) (select RFID_NO, name, price, quantity, totalPrice , NOW() ,DATE_ADD(NOW(), interval 1 month),customer_name,ph_no from cart);
END if; 
END ;

After this i do a multiple row insertion