MySQL trigger throws error 1064

Question

First of all, I know there are many questions of the same type out there, but they didn't solve my issue!

I want to delete the oldest row if there are more than 10 rows for a given set of 2 columns. My trigger looks like this:

 DELIMITER //

 CREATE TRIGGER TEST_TRIGGER
 AFTER INSERT
 ON table1
 FOR EACH ROW

 BEGIN

 SELECT COUNT(*)
 INTO @Tcount
 FROM table1
 WHERE columnA = NEW.columnA
 AND columnB = NEW.columnB;

 IF @Tcount > 10 THEN

 DELETE FROM table1
 WHERE id in (select * from (select MIN(id) from table1 where columnA = NEW.columnA and columnB = NEW.columnB) as t);


 END IF;
 END; 
 //
 DELIMITER;

I have the trigger in a file and the error is thrown when I execute it. The full error is

ERROR 1064 (42000) at line 25: 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 'DELIMITER' at line 1

Line 25 is the last line that has DELIMITER;

My table looks like this:

CREATE TABLE `table1` (
  `columnA` varchar(255) NOT NULL,
  `columnB` varchar(255) NOT NULL,
  `Version` varchar(255) NOT NULL,
  `Build` int(11) DEFAULT NULL,
  `Date` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
)

The trigger gets added even after throwing the error. But when I add an 11th entry (for a given combination of columnA and columnB) into the table I get the following error:

ERROR 1442 (HY000): Can't update table 'table1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

The select and delete statements in the trigger are fine when executed individually, with appropriate values of course.

MySQL version 5.5.46

I'd be very thankful for any corrections / suggestions.

Answer 1

I would remove the semicolon after end.

... END | DELIMITER ;

Try that out.

This reference may help you: mysql delimiter error