468,514 Members | 1,697 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,514 developers. It's quick & easy.

how to create a fixed size table in mysql

126 64KB
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?
Oct 31 '12 #1
17 4919
Rabbit
12,513 Expert Mod 8TB
What do you mean by fixed size table?
Oct 31 '12 #2
PreethiGowri
126 64KB
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
Oct 31 '12 #3
Rabbit
12,513 Expert Mod 8TB
You can use an insert trigger to run a count and delete operation.
Oct 31 '12 #4
PreethiGowri
126 64KB
i'm new to that can you guide me please
Nov 1 '12 #5
Rabbit
12,513 Expert Mod 8TB
Here is MySQL's documentation on triggers: http://dev.mysql.com/doc/refman/5.0/en/triggers.html
Nov 1 '12 #6
PreethiGowri
126 64KB
how do we put a count for insert in trigger query?
Nov 2 '12 #7
Rabbit
12,513 Expert Mod 8TB
It's just a simple select count all query.
Nov 2 '12 #8
PreethiGowri
126 64KB
Expand|Select|Wrap|Line Numbers
  1. DELIMITER $$ 
  2.  create  TRIGGER MyTrigger before insert on purchased
  3.  delete from purchased where count = 3
  4.  $$
  5.  DELIMITER;
  6.  
the count(*) value is aliased to count in my above query.
This query isn't working for me, can you correct this please:(
Nov 2 '12 #9
PreethiGowri
126 64KB
Finally done with this home work of trigger:):)

Expand|Select|Wrap|Line Numbers
  1. "create  TRIGGER MyTrigger before insert on purchased
  2.  FOR EACH ROW 
  3.  BEGIN 
  4.  if count  > 3 then 
  5.  delete from purchased
  6.  insert into purchased values(id, name , quantity);
  7.  END if;
  8.  END ;
Thanks for your guidance Rabbit:):)
Nov 2 '12 #10
PreethiGowri
126 64KB
do we have an option of if else in trigger query?
Nov 2 '12 #11
Rabbit
12,513 Expert Mod 8TB
Yes, you can use if else in a trigger.
Nov 2 '12 #12
PreethiGowri
126 64KB
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?
Nov 3 '12 #13
Rabbit
12,513 Expert Mod 8TB
Please post your most current code.
Nov 3 '12 #14
PreethiGowri
126 64KB
Expand|Select|Wrap|Line Numbers
  1. create  TRIGGER MyTrigger before insert on purchased
  2. FOR EACH ROW
  3. BEGIN
  4. if count  > 6 then
  5. delete from purchased limit 1
  6. elsif count <= 6 then
  7. 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);
  8. END if; 
  9. END ;
  10.  
After this i do a multiple row insertion
Nov 5 '12 #15
Rabbit
12,513 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. DELIMITER |
  2.  
  3. CREATE TRIGGER triggerName
  4. BEFORE INSERT ON tableName
  5. FOR EACH ROW
  6.  
  7. BEGIN
  8. DECLARE num_rows INTEGER;
  9.  
  10. SELECT COUNT(*)
  11. INTO num_rows
  12. FROM tableName;
  13.  
  14. IF num_rows > 6 THEN
  15.    DELETE FROM tableName LIMIT 1;
  16. END IF;
  17.  
  18. |
  19. DELIMITER ;
There's no need to do another insert. It's already going to insert.
Nov 5 '12 #16
PreethiGowri
126 64KB
i have a doubt can multiple tables of same database have triggers??
Nov 6 '12 #17
Rabbit
12,513 Expert Mod 8TB
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.
Nov 6 '12 #18

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by nm | last post: by
2 posts views Thread by Danny | last post: by
11 posts views Thread by Linny | last post: by
3 posts views Thread by patl | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.