Thursday, May 10, 2012

Send Email from MySQL Trigger

  1. CREATE TRIGGER send_emailverifier AFTER INSERT, UPDATE ON tbl_users
  2. FOR EACH ROW BEGIN
  3. SELECT * FROM email_bodies WHERE EmailID = 1;
  4. SELECT * FROM tbl_users WHERE ClientID = @ClientID
  5. INSERT INTO tbl_emailverify VALUES (UUID, tbl_users.ClientID, OLD.CltEmail, NEW.CltEmail)
  6. SELECT concat("To: ",NEW.CltEmail & "," & OLD.CltEmail),
  7.                 "From: triggers@yourmysqlserver.whatever",
  8.                 concat("Subject: ",NEW.subject),
  9.                 "",
  10.                 email_bodies.EmailContent
  11. INTO OUTFILE "/inetpub/mailroot/pickup/mail.eml"
  12. FIELDS TERMINATED BY '\r\n';
  13. END

1 comment: