MySQL Trigger Syntax Error IF Exists

Question

I have a MySQL Trigger I am trying to create, but can't get the syntax right.

The trigger should go through and match a set of keywords against the title of the new post inserted into the database. If it finds a match, it should assign the new post to that bucket and update the buckets set of keywords. If it does not find a match, it should create a new bucket using values from the post. The errors point to the line beginning with IF EXISTS, but I can't figure out what's wrong. Any help would be greatly appreciated. Thanks!

UPDATE: Code has been updated to pull out IF EXISTS, but now I am getting an error starting at the ELSE statement.

delimiter $$

CREATE TRIGGER bucket_trigger_v1 BEFORE INSERT ON posts
FOR EACH ROW
  BEGIN
    set @target = NEW.post_title;
    set @bucket_id_number = '';
    set @bucket_relevance = '';

    SELECT idbuckets, MATCH (keywords) AGAINST (@target) AS score INTO @bucket_id_number, @bucket_relevance FROM buckets WHERE MATCH (keywords) AGAINST (@target) ORDER BY score DESC LIMIT 1;
    IF (@bucket_id_number != '') THEN
      BEGIN
        SET NEW.buckets_idbuckets = @bucket_id_number;
        UPDATE buckets SET keywords = concat(keywords, @target) WHERE idbuckets = @bucket_id_number;
      END
      ELSE
      BEGIN
        INSERT INTO buckets (idbuckets, keywords, creationdate) VALUES (, @target, NEW.postdate);
        SET NEW.bucket_idbuckets = (SELECT LAST_INSERT_ID());
      END
    END IF;
END;

$$

wchiquito · Accepted Answer · 2013-11-06 09:41:54Z

1

A couple of points to consider:

13.2.9.1. SELECT ... INTO Syntax

An INTO clause should not be used in a nested SELECT because such a SELECT must return its result to the outer context.

19.3.1. Trigger Syntax and Examples

Such a SET statement has no effect in an AFTER trigger because the row change will have already occurred.

UPDATE

The following code creates correctly the trigger. The posts table is created only in order to create the trigger.

/*Table structure for table `posts` */

DROP TABLE IF EXISTS `posts`;

CREATE TABLE `posts` (
  `post_title` VARCHAR(500) DEFAULT NULL,
  `buckets_idbuckets` INT(11) DEFAULT NULL,
  `postdate` DATETIME DEFAULT NULL
) ENGINE=INNODB;

/* Trigger structure for table `posts` */

DELIMITER $$

/*!50003 DROP TRIGGER*/ /*!50032 IF EXISTS */ /*!50003 `bucket_trigger_v1` */$$

CREATE TRIGGER `bucket_trigger_v1` BEFORE INSERT ON `posts`
FOR EACH ROW
BEGIN
    SET @target := new.`post_title`;
    SET @bucket_id_number := '';
    SET @bucket_relevance := '';
    SELECT `idbuckets`, MATCH (`keywords`) AGAINST (@target) AS `score` INTO @bucket_id_number, @bucket_relevance FROM `buckets` WHERE MATCH (`keywords`) AGAINST (@target) ORDER BY `score` DESC LIMIT 1;
    IF (@bucket_id_number != '') THEN
        SET new.`buckets_idbuckets` := @bucket_id_number;
        UPDATE `buckets` SET `keywords` = CONCAT(`keywords`, @target) WHERE `idbuckets` = @bucket_id_number;
    ELSE
        INSERT INTO `buckets` (`keywords`, `creationdate`) VALUES (@target, new.`postdate`);
        SET new.`buckets_idbuckets` := LAST_INSERT_ID();
    END IF;
END$$

DELIMITER ;

edited Nov 6, 2013 at 9:41

answered Nov 5, 2013 at 21:05

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

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

6 Comments

Drizzit12 Over a year ago

I updated the code to remove the into clause, but now I am getting another error at the ELSE portion of the code. I appreciate your help.

wchiquito Over a year ago

The INSERT statement has an error, missing the first value to be inserted.

Drizzit12 Over a year ago

That value is an AUTO_INCREMENT column. Do I need to put something specific there? I thought it would auto fill.

wchiquito Over a year ago

A sentence also like the following should work: INSERT INTO buckets (keywords, creationdate) VALUES (@target, NEW.postdate);

Drizzit12 Over a year ago

Appreciate it, I think I figured it out though. I'll post the answer.

|

Drizzit12 · Accepted Answer · 2013-11-05 23:37:07Z

0

MySQL, or at least the version I'm running, can't handle multiple triggers at once. The IF statement doesn't work with BEGIN and END because of this, and so I'll have to find a different solution.

answered Nov 5, 2013 at 23:37

Drizzit12

1893 silver badges19 bronze badges

Collectives™ on Stack Overflow

MySQL Trigger Syntax Error IF Exists

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