CODEWITHSUNDEEP
mysqlsqltriggers
MyiEye
MyiEye

Reputation: 482

MySQL Syntax error near ''

I'm a lot more used to T-SQL than MySQL and it seems as though there are slight syntax issues that I just can't quite figure out. I'm getting an error message that seems quite meaningless to me and I would really appreciate it if someone could just tell me what I'm doing wrong here. Am I perhaps not allowed to do an UPDATE in an UPDATE TRIGGER?

The idea is that I want to just keep track of whether or not my current German log has been corrected or not and record the times based on whether or not I'm updating my portion of the log or my tutor is updating their portion.

My Code is:

DELIMITER //

CREATE TRIGGER updateTimes
AFTER UPDATE ON Logs
FOR EACH ROW

BEGIN

    IF (Old.TimGerman <> New.TimGerman OR
        Old.TimComment <> New.TimComment)
    THEN
            UPDATE Logs 
                SET DateUpdated = CURRENT_TIMESTAMP, Corrected = 0
                WHERE LogID = New.LogID;

    ELSE IF (Old.TutorGerman <> New.TutorGerman OR
                Old.TutorComment <> New.TutorComment)
    THEN
            UPDATE Logs
                SET DateMarked = CURRENT_TIMESTAMP, Corrected = 1
                WHERE LogID = New.LogID;
    END IF;
END //
DELIMITER ;

Me error message says:

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax near '' at line 21.

Line 21 is 4th from the bottom: WHERE LogID = New.LogID;

Thanks very much for the help!

Upvotes: 0

Views: 4414

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

Syntactically your trigger is correct except on ELSE IF part. You need an extra END IF if you want to use it. Otherwise modify it as ELSEIF.

And also it seems you need a BEFORE trigger but not AFTER trigger.
Apart from that, calling the explicit update in an update trigger on the same table is meaning less as it would cause a circular event. Which is not supported and throws an error.

Change your trigger definition as below: 

DELIMITER //

CREATE TRIGGER updateTimes BEFORE UPDATE ON Logs
FOR EACH ROW
BEGIN
    IF ( OLD.LogID = New.LogID ) THEN
         IF ( Old.TimGerman <> New.TimGerman OR
              Old.TimComment <> New.TimComment )
         THEN
             SET NEW.DateUpdated = CURRENT_TIMESTAMP, NEW.Corrected = 0;

         ELSEIF ( Old.TutorGerman <> New.TutorGerman OR
                  Old.TutorComment <> New.TutorComment )
         THEN
             SET NEW.DateMarked = CURRENT_TIMESTAMP, NEW.Corrected = 1;
         END IF;
     END IF;
END;
//

DELIMITER ;

Upvotes: 1

Related Questions