mysql trigger AFTER INSERT if condition syntax error

Question

#1064 - 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 '' at line 7

DELIMITER //
 CREATE TRIGGER after_tableA_insert AFTER INSERT ON tableA
 FOR EACH ROW
 BEGIN
 IF(TIMESTAMPDIFF(HOUR, `triedTime`, CURRENT_TIMESTAMP()) < 60000) THEN
 INSERT into error
 SET triedTime = NEW.changedat;
 END //
DELIMITER ;

• Rick James: 60K hours is a long time??

Answer 1

Based on your own answer, the issue appears to have been with this line:

IF(TIMESTAMPDIFF(HOUR, `triedTime`, CURRENT_TIMESTAMP()) < 60000) THEN

The problem is the `triedTime` reference. According to the manual:

Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger.

That is to say, without either NEW or OLD you cannot access columns of the affected rows. So, in the above shown line you probably want to use the NEW keyword:

IF (TIMESTAMPDIFF(HOUR, NEW.`triedTime`, CURRENT_TIMESTAMP()) < 60000) THEN

Your own method of looking the value up in the table works as well, only it means an extra table hit, which may affect performance.

• ypercubeᵀᴹ: +1 for this. If you want to use a variable, you could use for example: varA = TIMESTAMPDIFF(HOUR, NEW.triedTime, CURRENT_TIMESTAMP())

Answer 2

I done with it!

 CREATE TRIGGER `after_tableA_insert` AFTER INSERT ON `tableA`
 FOR EACH ROW BEGIN
 declare curSdate integer;
 set curSdate = (SELECT 
 IF(TIMESTAMPDIFF(SECOND,`triedTime`,CURRENT_TIMESTAMP())<1,1,0) as 
 res from tableA where trialId = NEW.trialId);
 IF curSdate = 1 THEN
 INSERT into error SET errorNumber = NEW.trialId
 , userId = NEW.userId, changedat = NEW.triedTime;
 ELSEIF curSdate = 0 THEN
 INSERT into error_logs SET errorNumber = 0, userId = 0;
 END IF;
 END

• ypercubeᵀᴹ: The set curSdate = (SELECT ...) will fail if trialid is not unique. See AndriyM's answer for identifying the cause of the problem.
• Rick James: Within 1 second?
• Mayuresh Hedaoo: yes, In above case trailId is primary key