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

[Rabbit]

Your syntax is off. This is off the top of my head as I don't have MySQL to test it against where I am currently. The basic syntax will look kind of like this:

DELIMITER |
 
CREATE TRIGGER triggerName
BEFORE INSERT ON tableName
FOR EACH ROW
 
BEGIN
DECLARE num_rows INTEGER;
 
SELECT COUNT(*)
INTO num_rows
FROM tableName;
 
IF num_rows > 6 THEN
   DELETE FROM tableName LIMIT 1;
END IF;
 
|
DELIMITER ;

There's no need to do another insert. It's already going to insert.

[PreethiGowri]

i have a doubt can multiple tables of same database have triggers??

[Rabbit]

I'm not sure what that means but if there's a hard limit on the number of triggers you can have, it would be a fairly high number. But I doubt you have any other triggers than this one since you said you said you were new to triggers. So I must have misunderstood your meaning.