1

The following oddly does work, but shows an error in PhpMyAdmin... (see image below)

PhpMyAdmin Error Showing Despite it Working

(in the definition block as seen in the image)

BEGIN
SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND);
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2)) 
SECOND);
END

END has a red X. The syntax is "Unrecognized statement type. (near END)


WHAT I HAVE TRIED TO FIX THIS

SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND);
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2)) 
SECOND);

The errors go away in phpMyAdmin but when I click GO it gives a huge error so that's obviously not correct.


I have read people are using define when more than 1 statement is needed so I tried...

DELIMITER $$
BEGIN
SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND);
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2)) SECOND);
END$$

DELIMITER ;

I get unexpected characters ; then I tried...

DELIMITER $$
BEGIN
SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND)$$
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2)) SECOND)$$
END$$

DELIMITER ;

Which doesn't work and gives me the original error before I tried fixing anything. So, I don't know what to say. I am obviously a novice to all this. I just spent over an hour reading about triggers, mysql docs, but I don't know what I am doing wrong. I spent another 30 min reading other questions about similar things to see if I can spot the syntax problem I am causing by looking at other peoples examples in how they create triggers in this. I don't know what I am doing wrong. :)

Maybe it's valid but it gives an error in PhpMyAdmin incorrectly!!?!??!

Any ideas? Thank you!

It works... but, maybe it's not supposed to. I don't know the proper syntax to do this. Any tips would be appreciated.

I wrote all this because I want to do things properly so I figured I would ask someone.

Thank you for your time.

1 Answer 1

0

Instead of editing the existing trigger, try to open a new SQL Console and try this :

DROP TRIGGER IF EXISTS `AuditSetExpiryTokens`;
DELIMITER $$
CREATE DEFINER = CURRENT_USER TRIGGER `AuditSetExpiryTokens` BEFORE 
INSERT ON `token` FOR EACH ROW
BEGIN
 SET NEW.expiry=DATE_ADD(NOW(),INTERVAL NEW.duration SECOND);
 SET NEW.delete_date=DATE_ADD(NOW(),INTERVAL(NEW.duration+(3600*2)) SECOND);
END$$
DELIMITER ;
3
  • Hey there, sorry took so long to try this. I did what you said... I went into the SQL tab so I could paste in a query, it shows an error on line 6 and 7 (unexpected character ;) but I clicked GO anyways and it succeeded. When I went into Triggers it shows the same error as before. Maybe it's just an error in PHPMyAdmin... the query works, it adds the trigger, it just shows an error on line 4 where the END is. It seems it also stripped out everything before BEGIN and after END and put it into the Definition field and populated the proper fields above. Strange.... thank you for trying!!!
    – PerryCS
    Feb 20, 2018 at 4:42
  • My roommate mentioned to me that it's most likely a syntax error in the PHPMyAdmin editor highlighter. Your code works, my code works, but the editor in PHPMyAdmin shows an error on valid code. He mentioned if the trigger was wrong it wouldn't even let me add it. So, most likely a bug in PHPMyAdmin's syntax highlighter. I'll mark your response as the answer since you spent time to help me out.
    – PerryCS
    Feb 20, 2018 at 5:37
  • I wish I had more rep to give you points. I marked it but hopefully you get credit despite it not adjusting the counter. Shame I can't use my REP from the main StackOverflow site since I have an ok amount on there.
    – PerryCS
    Feb 20, 2018 at 5:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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