DELETE TRIGGER in MySql throwing error 1064?
You're missing FOR EACH ROW before DELETE : dev.mysql.com/doc/refman/5.1/en/create-t... 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.
You're missing FOR EACH ROW before DELETE: dev.mysql.com/doc/refman/5.1/en/create-t... 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.
That is an optional command as I recall. After adding it I get error: 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 4 – JClaspill May 13 at 18:05 @JClaspill It is not optional (as indicated in the link I posted). Edited to include delimiter portion and added the semicolon after your delete statement.
– Chris Morgan May 13 at 18:14 I was able to add it, but it never seems to trigger. So you get the solved answer here in a bit, but curious as to if you notice anything I'm doing wrong for it to not trigger(or if it does, it doesn't do anything when it triggers)? – JClaspill May 13 at 18:37 @JClaspill Without seeing your data, it's hard to tell.
What does SELECT 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; return? – Chris Morgan May 13 at 18:48 It gives me a resultset of rows outside the newest 50.
– JClaspill May 13 at 19:01.
I had the same problem with the following statement, it ALWAYS gave me a syntax error on even this simplified delete statement (originally was DELETE FROM APP_CACHE_VIEW WHERE APP_UID = OLD. APP_UID;): CREATE TRIGGER APPLICATION_DELETE BEFORE DELETE ON APPLICATION FOR EACH ROW BEGIN DELETE FROM APP_CACHE_VIEW; END If I changed the SQL command to the following then it WORKED but I don't understand why: DELIMITER $$ CREATE TRIGGER APPLICATION_DELETE BEFORE DELETE ON APPLICATION FOR EACH ROW BEGIN DELETE FROM APP_CACHE_VIEW; END$$ DELIMITER.
Just add foreign key to user_rights table, and error will be raised by server. Also, make the field valid_rights. Right_code unique, if it is not.
You're missing FOR EACH ROW before DELETE : http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html.
I had the same problem with the following statement, it ALWAYS gave me a syntax error on even this simplified delete statement (originally was DELETE FROM APP_CACHE_VIEW WHERE APP_UID = OLD. APP_UID;).
I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.