DELETE TRIGGER in MySql throwing error 1064

Question

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'

I ended up having PHP do this at the start of the input rather than the trigger, as Chris Morgan found down below that TRIGGERs don't seem to work for my exact scenario. — JClaspill
– JClaspill, Commented May 13, 2011 at 19:28

Chris Morgan · Accepted Answer · 2011-05-13 19:09:28Z

3

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.

edited May 13, 2011 at 19:09

answered May 13, 2011 at 17:58

Chris Morgan

2,08015 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

JClaspill Over a year ago

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

Chris Morgan Over a year ago

@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.

JClaspill Over a year ago

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 doesnt do anything when it triggers)?

Chris Morgan Over a year ago

@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?

JClaspill Over a year ago

It gives me a resultset of rows outside the newest 50.

|

stealthyninja · Accepted Answer · 2012-01-23 09:51:49Z

1

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 ;

edited Jan 23, 2012 at 9:51

stealthyninja

10.4k11 gold badges57 silver badges61 bronze badges

answered Aug 20, 2011 at 17:47

WebSolProv

131 silver badge4 bronze badges

Collectives™ on Stack Overflow

DELETE TRIGGER in MySql throwing error 1064

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related