SQL trigger and auto email - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL trigger and auto email

Author

Topic

egghi
Starting Member

17 Posts

Posted - 2006-10-16 : 13:30:36

Hi,

I have a trigger for a table which stores email information generated from an ACCESS form. The trigger should send an auto email response to users who submitted an email to request for their password (we have forgetful users!). There is something wrong with my trigger because the auto email is sent out with a blank body... I will appreciate any advice!

Thank you!

My trigger:
CREATE TRIGGER tr_SendPassword ON PIPEmail
FOR INSERT
AS
DECLARE @Password varchar(100)
DECLARE @EmailAddress varchar(100)
DECLARE @message varchar(100)
IF (select count(*) from inserted) = 1
BEGIN
IF exists (SELECT * FROM inserted
WHERE Subject = 'Forgot my password')
BEGIN
select @Password = 'We received an email request from you for your password. Your password for SAR Search is: ' + UserRole.Password,
@EmailAddress = [User].Email
from UserRole
join inserted on UserRole.WindowsUser = inserted.WindowsUser
join [User] on [User].WindowsUser = UserRole.WindowsUser

exec master.dbo.xp_sendmail @recipients=@EmailAddress,
@subject='SAR Search password request',
@message=@Password
END
END

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-16 : 14:07:00

It is not a good idea to send email from a trigger.

You should create a stored procedure to do this, and call that from your form.

CODO ERGO SUM

egghi
Starting Member

17 Posts

Posted - 2006-10-16 : 15:35:29

Hi,

I am not very good at writing stored procedure... So far, I have

CREATE PROCEDURE s_AutoEmail ON PIPEmail
AS

DECLARE @Password varchar(100)
DECLARE @EmailAddress varchar(100)
DECLARE @body varchar(1024)
IF (select Subject from PIPEmail) = 'Forgot my password'
BEGIN
SET @body = 'select @Password = 'We received an email request from you for your password. Your password for SAR Search is: ' + UserRole.Password,
@EmailAddress = [User].Email
from UserRole
join PIPEmail on UserRole.WindowsUser = PIPEmail.WindowsUser
join [User] on [User].WindowsUser = UserRole.WindowsUser'

exec master.dbo.xp_sendmail @recipients=@EmailAddress,
@subject='SAR Search password request',
@message=@body
END
END

Also, how do I add a statement to the stored procedure that will flag the record on PIPEmail table after an auto email response has been sent to user?

Thank you!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-16 : 19:12:51

You don't create s stored procedure on a table:

CREATE PROCEDURE s_AutoEmail ON PIPEmail

You will need to make the user logon a parameter of the stored procedure, and pass that as a partameter to the procedure.

CODO ERGO SUM

egghi
Starting Member

17 Posts

Posted - 2006-10-17 : 13:36:07

I see. I will give it a try:) Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources