Question

Locked

Send mail from MS SQL on record insert with a trigger

By simonnd ·
Hey guys. I would like to create a trigger for sending user details to them once their account is created in the application.
The table is named as follows:
[Sales_Lead].[dbo].[SalesUserLogin]
and contains the following fields
[UserID]
,[FirstName]
,[Surname]
,
,[UserName]
,[Password]
,[SecurityLevelID]
,[Active]
Basically I would like to send back to the the following.

from: noreply@mydomain.co.za
to: contained in table
subject: 'Your sales leads registration details'
body: 'Thank you '@Name' '@Surname' for reqeusting a login account for the RMCS Sales Leads Application with access level '@Level'.
Note that all details needs to be kept confidentail. Your username is '@user' and your password is '@pass'.

Thank you
RMCS IT'

This is what i've tried

CREATE TRIGGER New_Account
ON [Sales_Lead].[dbo].[SalesUserLogin]
AFTER INSERT

AS
BEGIN

@Email varchar(50)
,@Name varchar (50)
,@Surname varchar(50)
,@user varchar(50)
,@pass varchar(50)
,@Level int
,@body varchar(500)

Select @Email = Email, @Name = Firstname, @Surname = Surname, @user = Username, @pass = Password, @Level = SecurityLevelID
From inserted
SET @body = 'Thank you '@Name' '@Surname' for reqeusting a login account for the RMCS Sales Leads Application with access level '@Level'.
Note that all details needs to be kept confidentail. Your username is '@user' and your password is '@pass'.

Thank you
RMCS IT'

EXEC master..xp_sendmail
@recipients = @Email,
@subject = 'User Sales Lead Account Created',
@message = @body


END
GO

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums