phpMyAdmin trigger error

Question

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

(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.

Answer 1

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 ;