how to send email by using triggers in mysql?

Question

i am new in trigers working.how to send email by using trigers in mysql?

Answer 1

I've never used triggers, but this link could help.

http://www.datasprings.com/Resources/ArticlesInformation/CreatingEmailTriggersinSQLServer2005.aspx

Answer 2

First, why do you need to do this. If you are using PHP, ASP.NET, JSP, etc... it may be easier to put this functionality in that code.

Otherwise use MySQL's SELECT INTO OUTFILE functionality.

EXAMPLE:

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

If not on Windows then the /inetpub/mailroot/pickup/mail.eml folder path would need to be changed for your platform.

If using the mailroot/pickup folder save to a temp folder first to ensure the file is completed before being emailed.

Or you could post the contents to a PHP (or ASP, JSP, etc...) page that will email it for you.