NadezhdaGeorgieva

16 Emails Trigger

Jun 15th, 2021
925
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE `logs`(
  2.     log_id INT(11) PRIMARY KEY AUTO_INCREMENT,
  3.     account_id INT(11) NOT NULL,
  4.     old_sum DECIMAL(19,4) NOT NULL,
  5.     new_sum DECIMAL(19,4) NOT NULL,
  6.     CONSTRAINT fk_logs_accounts
  7.     FOREIGN KEY(account_id)
  8.     REFERENCES accounts(id)
  9. );
  10.  
  11. CREATE TABLE `notification_emails`(
  12.     `id` INT(11) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  13.     `recipient` INT(11) NOT NULL,
  14.     `subject` VARCHAR(50) NOT NULL,
  15.     `body` VARCHAR(255) NOT NULL
  16. );
  17.  
  18. CREATE TRIGGER tr_notification_emails
  19. AFTER INSERT
  20. ON `logs`
  21. FOR EACH ROW
  22. BEGIN
  23.     INSERT INTO `notification_emails`(`recipient`, `subject`, `body`)
  24.     VALUES (NEW.account_id,
  25.             CONCAT('Balance change for account: ', NEW.account_id),
  26.             CONCAT('On ', DATE_FORMAT(NOW(), '%b %d %Y at %r'), ' your balance was changed from ',
  27.                 ROUND(NEW.old_sum, 2), ' to ', ROUND(NEW.new_sum, 2), '.'));
  28. END
RAW Paste Data