rUhyiU79

1CREATE TABLE NotificationEmails (
2Id INT PRIMARY KEY IDENTITY,
3Recipient INT NOT NULL,
4[Subject] VARCHAR(100) NOT NULL,
5Body VARCHAR(200) NOT NULL,
6CONSTRAINT FK_NotificationEmails_Accounts
7FOREIGN KEY (Recipient) REFERENCES Accounts(Id)
8)
9
10GO
11
12CREATE TRIGGER tr_CreateEmail ON Logs
13FOR UPDATE
14AS
15	IF EXISTS
16     (
17         SELECT *
18           FROM Logs
19     )
20         -- UPDATE Statement is executed
21         INSERT INTO NotificationEmails(Recipient, [Subject], Body)
22              SELECT l.AccountId AS [Recipient], 
23			         CONCAT('Balance change for account: ', l.AccountId) AS [Subject],
24					 CONCAT('On ', GETDATE(), ' your balance was changed from ', l.OldSum, ' to ', l.NewSum) AS [Body]
25			    FROM Logs AS l
26                     INNER JOIN Accounts AS a ON l.AccountId = a.Id
27GO