I get an error (1064) when attempting to run the following... (MySql 5.5.9)
query:
CREATE TRIGGER clearChat AFTER INSERT ON chat
FOR EACH ROW
BEGIN
DELETE p.* FROM chat p LEFT JOIN (SELECT t.id FROM chat t ORDER BY t.id DESC LIMIT 50) x ON x.id = p.id WHERE x.id IS NULL
END;
the error is:
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 'END' at line 5
Any assistance would be great.
Last Edit: Updated to show the 'FOR EACH ROW' and 'BEGIN'
You're missing FOR EACH ROW
before DELETE
: http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html
Edit: There are more issues. The correct syntax is below:
delimiter |
CREATE TRIGGER clearChat AFTER INSERT ON chat
FOR EACH ROW BEGIN
DELETE p.* FROM chat p LEFT JOIN (SELECT t.id FROM chat t ORDER BY t.id DESC LIMIT 50) x ON x.id = p.id WHERE x.id IS NULL;
END;
|
delimiter ;
Edit 2:
I don't think that query is allowed to be in a trigger at all based on this http://dev.mysql.com/doc/refman/5.1/en/faqs-triggers.html#qandaitem-B-5-1-9:
A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.
Since you aren't using OLD
or NEW
, I don't think you can modify chat
since the trigger is triggered on inserts to chat
.