What is the difference between the MySQL Trigger exception and MySQL Event?

5

I would like to create an Email trigger scheduler , if possible, without using cron , and would like to know if you can do this through MySQL >.

Searching the Internet I've seen some examples of creating a non-cron event :

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 email using trigger , I just do not know if it works because I did not test:

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: [email protected]", 
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 you could tell an email trigger to trigger the submit URL, for example:

/enviar-emails/go
    
asked by anonymous 14.12.2015 / 13:38

1 answer

4

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 fire - as its name says - an execution of something when something occurs in a table. A change in the data ( INSERT , UPDATE , DELETE ) causes the secondary action.

EVENT is a relatively new feature that is determined by time, is a scheduler. It is a way of ensuring that an action is executed from time to time, regardless of what happens in the database. It is a kind of cron of the database.

So it depends on the purpose to trigger the email. Every time an email is inserted or updated in the table, do you need to fire it? Use TRIGGER , as shown in the above code. If sending - even if selectively - must be done from time to time, regardless of what happens in the database, use EVENT .

The action you both perform can be basically the same. Of course there are some limitations in EVENT because it is not manipulating data. For example, it does not have an old or new version of the data that can be used in TRIGGER . But it's a limitation that does not upset you because it would not even make sense to have this in something that is not updating the data.

    
14.12.2015 / 14:05