By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,902 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

how to create a fixed size table in mysql

100+
P: 126
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
Share this Question
Share on Google+
17 Replies


Rabbit
Expert Mod 10K+
P: 12,347
What do you mean by fixed size table?
Oct 31 '12 #2

100+
P: 126
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
Expert Mod 10K+
P: 12,347
You can use an insert trigger to run a count and delete operation.
Oct 31 '12 #4

100+
P: 126
i'm new to that can you guide me please
Nov 1 '12 #5

Rabbit
Expert Mod 10K+
P: 12,347
Here is MySQL's documentation on triggers: http://dev.mysql.com/doc/refman/5.0/en/triggers.html
Nov 1 '12 #6

100+
P: 126
how do we put a count for insert in trigger query?
Nov 2 '12 #7

Rabbit
Expert Mod 10K+
P: 12,347
It's just a simple select count all query.
Nov 2 '12 #8

100+
P: 126
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

100+
P: 126
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

100+
P: 126
do we have an option of if else in trigger query?
Nov 2 '12 #11

Rabbit
Expert Mod 10K+
P: 12,347
Yes, you can use if else in a trigger.
Nov 2 '12 #12

100+
P: 126
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
Expert Mod 10K+
P: 12,347
Please post your most current code.
Nov 3 '12 #14

100+
P: 126
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
Expert Mod 10K+
P: 12,347
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

100+
P: 126
i have a doubt can multiple tables of same database have triggers??
Nov 6 '12 #17

Rabbit
Expert Mod 10K+
P: 12,347
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.