1

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.

Willem Renzema
  • 5,177
  • 1
  • 17
  • 24
gkris
  • 1,209
  • 3
  • 17
  • 44
  • Please do not change your question as a result of correct answers. Gwedoh answered your question correctly, accept his answer, and then open a new question for your new problem. Stackoverflow is meant to be a resource for future users, and changing your question in response to correct answeres makes it hard for future uses to obtain any value from your question. – Willem Renzema Nov 15 '15 at 16:59
  • I have mentioned both the issues in the original question itself. Yes I changed my question title because it is misleading. You are correct in saying I should accept Gwedoh's answer, but that would give an impression that the question is fully answered. Since I am expecting some more response, I haven't accepted the answer. – gkris Nov 15 '15 at 17:04
  • 1
    You'll need to run that logic outside of the trigger, since a trigger simply cannot modify the underlying table outside of the row it is currently operating on. Depending on the amount of concurrency of this table, you may want to use `SELECT ... ORDER BY recent_criteria LIMIT 10` when querying the table, and having a maintenance job periodically purge older rows. – JRD Nov 15 '15 at 17:22
  • @JRD:thanks, I understood my problem, I can't delete and insert into the same table! – gkris Nov 15 '15 at 17:25

1 Answers1

4

I would remove the semicolon after end. 

... END | DELIMITER ;

Try that out.

This reference may help you: mysql delimiter error

Community
  • 1
  • 1
Anthos
  • 111
  • 2
  • Hey thanks, I removed the semicolon after `END` and I added a space after `DELIMITER` before the semi colon and it works! I still get `ERROR 1442` but that is something different I suppose – gkris Nov 15 '15 at 16:50
  • 2
    @gkris for your 1442 http://stackoverflow.com/questions/12203859/mysql-trigger-cannot-update-table-getting-error-1442 – Blag Nov 15 '15 at 17:03
  • @Blag: I understood my problem, I can't delete and insert into the same table! – gkris Nov 15 '15 at 17:25