#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 ;
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
OLDandNEWkeywords 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.
varA = TIMESTAMPDIFF(HOUR, NEW.triedTime, CURRENT_TIMESTAMP())
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
set curSdate = (SELECT ...) will fail if trialid is not unique. See AndriyM's answer for identifying the cause of the problem.