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.