1

#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 ;
  • 1
    60K hours is a long time?? – Rick James Dec 1 '17 at 22:52
2

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.

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

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
  • 1
    The set curSdate = (SELECT ...) will fail if trialid is not unique. See AndriyM's answer for identifying the cause of the problem. – ypercubeᵀᴹ Dec 1 '17 at 16:18
  • 1
    Within 1 second? – Rick James Dec 1 '17 at 22:53
  • yes, In above case trailId is primary key – Mayuresh Hedaoo Dec 2 '17 at 6:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.