asked on mysql trigger error
Hi everybody.
I have a table I want to limit to the last 20 records: all the rest has to be deleted. I thought to use a trigger and after a bit of Google surfing I tried this:
I have a table I want to limit to the last 20 records: all the rest has to be deleted. I thought to use a trigger and after a bit of Google surfing I tried this:
DELIMITER $$
CREATE TRIGGER limit_table
AFTER INSERT
DELETE FROM `notifications`
WHERE id <= (
SELECT id FROM (
SELECT id
FROM `notifications`
ORDER BY id DESC
LIMIT 20
)
);
$$
DELIMITER ;
SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE FROM `notifications` WHERE id <= (SELECT id FROM (SELECT id' at line 3Thank you.
DatabasesPHPMySQL ServerSQL
Last Comment
Marco Gasi
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
From the development and usage point: I insert 21 rows, get an okay and when want to read my data, it's gone. Pretty astonishing.
ASKER
Thank you everybody for your contributions :-)
@manumd: I tried your code but I get the same identical error message both in phpMyAdmin and in HeidiSQL
@Mukesh Yadav: that was my first trying and I moved to the posted one to see if it did some difference: not, same error again
@ste5an: I'll keep in mind your comment when I'll succeed in having a working code :-)
@manumd: I tried your code but I get the same identical error message both in phpMyAdmin and in HeidiSQL
@Mukesh Yadav: that was my first trying and I moved to the posted one to see if it did some difference: not, same error again
@ste5an: I'll keep in mind your comment when I'll succeed in having a working code :-)
ASKER
I've just realized that this question is wrong.
The fact is that the table stores a series of events and the max number of events per user must be 20, So actually the above query must be changed adding a WHERE clause id=$_SESSION['user_id']. And this makes me think the question is just wrong: having to refer to php session I have to code the routine manually in php code every time I run an INSERT query on that table.
But I leave the question open because I would like to know how to setup this operation for future needs.
The fact is that the table stores a series of events and the max number of events per user must be 20, So actually the above query must be changed adding a WHERE clause id=$_SESSION['user_id']. And this makes me think the question is just wrong: having to refer to php session I have to code the routine manually in php code every time I run an INSERT query on that table.
But I leave the question open because I would like to know how to setup this operation for future needs.
ASKER
Thank you all for your comments