Question:
I'd like to create an email trigger scheduler , if possible, without using cron , and I'd like to know if it's possible to do this through MySQL .
Searching the internet I saw some examples of event creation without using cron :
CREATE EVENT PurgeLogTable
ON SCHEDULE EVERY 1 WEEK
DO
BEGIN
DELETE FROM `logs` WHERE `LogTime` <= DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 1 WEEK);
INSERT INTO `audit` (`AuditDate`, `Message`) VALUES(NOW(), "Log table purged succesfully!");
END
And an example of triggering an email using trigger , I just don't know if it works, because I haven't tested it:
CREATE TRIGGER send_emailverifier AFTER INSERT, UPDATE ON tbl_users
FOR EACH ROW BEGIN
SELECT * FROM email_bodies WHERE EmailID = 1;
SELECT * FROM tbl_users WHERE ClientID = @ClientID
INSERT INTO tbl_emailverify VALUES (UUID, tbl_users.ClientID, OLD.CltEmail, NEW.CltEmail)
SELECT concat("To: ",NEW.CltEmail & "," & OLD.CltEmail),
"From: triggers@yourmysqlserver.whatever",
concat("Subject: ",NEW.subject),
"",
email_bodies.EmailContent
INTO OUTFILE "/inetpub/mailroot/pickup/mail.eml"
FIELDS TERMINATED by '\r\n';
END
But my question is pertinent as to how I could warn an email trigger, so that it triggers the sending URL, for example:
/enviar-emails/go
Answer:
They have very different purposes, as the syntax presented in the question already gives a hint.
TRIGGER is an old feature of databases in general to trigger – as its name says – an execution of something when something happens in a table. A change in data ( INSERT , UPDATE , DELETE ) triggers the secondary action.
EVENT is a relatively new feature that is time driven, it is a scheduler. It is a way of ensuring that an action is performed from time to time, regardless of what is happening in the database. It is a kind of database cron .
So it depends on the objective to trigger the email. Every time an email is inserted or updated in the table, do you need to trigger it? Use TRIGGER , as the code above shows. If sending – even if selectively – must be done from time to time, regardless of what happens in the database, use EVENT .
The action they both perform can be basically the same. Of course there are some limitations on EVENT because it is not handling data. For example, it doesn't have an old or new version of the data that can be used in the TRIGGER , but it's a limitation that doesn't get in the way because it wouldn't even make sense to have this in something that isn't updating the data.