Trigger to 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

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger to email.

Author

Topic

indangasy
Starting Member

4 Posts

Posted - 2014-11-03 : 06:28:24

I am working with two tables, Users & circulated Mails. I need a trigger that will pick the email address of the receiver from the users table. In the circulated table we have the receiver (Number). that Number is also in the users table with arelated email address. In the circulated table we have the sender (number) who the trigger should pick the names from the users table. I have treied as below but aint working. the trigger should email immidiately we have an insert in the circulated table

-- Create the trigger
CREATE TRIGGER newMail

--indicate which table the trigger is to be executed on
ON Circulated Mails

--indicate whether it is an UPDATE, INSERT or DELETE Trigger
FOR INSERT
AS

--holds the Sender Name
declare @Sender varchar(10)

--holds the body of the email
declare @body varchar(3000)

--holds Email Address
declare @Email varchar(1000)

SELECT @Email = (select Email from Users where [Personal No]=(select Receiver from [Circulated Mails])),
@Sender=(select Names from Users where [Personal No]=(select Sender from [Circulated Mails])),
SET @body =
'You have new Mail in the IRMS System'
'Sender ' + @Sender + '
'Login into the system http://kebsirms/irms'

--xp_sendmail is the extended sproc used to send the mail
EXEC master..xp_sendmail
@recipients = @Email,
@subject = 'You have New Mail',
@message = @body

END

GO

!Beninda.com~

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-03 : 09:06:31

if you execute the statements on their own, do you get the email? I notice that you do not specify the @set_user parameter. IIUC that means that SQL will try to send the mail as the user id running the SQL Server database engine, which may not have permission to send email.

indangasy
Starting Member

4 Posts

Posted - 2014-11-03 : 13:21:54

i have configured SQL mail and test mails are being sent without any issue.

so how do i go about it?

quote:
Originally posted by gbritton

if you execute the statements on their own, do you get the email? I notice that you do not specify the @set_user parameter. IIUC that means that SQL will try to send the mail as the user id running the SQL Server database engine, which may not have permission to send email.

!Beninda.com~

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-03 : 14:16:53

Have you tried specifying the @set_user parameter to a SQL user with permissions to send mail?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-03 : 14:30:33

Which version of SQL Server are you using? SQL Server 2000? Why not make the switch to Database Mail if you are using 2005 or newer?

Why aren't you using the inserted trigger table to determine what to email?

Sending emails via a trigger is a very bad design. Typically when there is this need, you write to a table within the trigger and then have a job that is constantly running that reads the table. The job is the one to send the email, not the trigger.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

indangasy
Starting Member

4 Posts

Posted - 2014-11-03 : 15:06:13

Tkizer, am using SQL 2008. the point is to have the notification / alert sent to the receiver about the new mail so that they can login.

How do i go about it?

quote:
Originally posted by tkizer

Which version of SQL Server are you using? SQL Server 2000? Why not make the switch to Database Mail if you are using 2005 or newer?

Why aren't you using the inserted trigger table to determine what to email?

Sending emails via a trigger is a very bad design. Typically when there is this need, you write to a table within the trigger and then have a job that is constantly running that reads the table. The job is the one to send the email, not the trigger.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

!Beninda.com~

indangasy
Starting Member

4 Posts

Posted - 2014-11-03 : 15:16:43

I have configured Database Mail and its sending out very well. when i excute the querry, am getting an error

quote:
Originally posted by gbritton

Have you tried specifying the @set_user parameter to a SQL user with permissions to send mail?

!Beninda.com~

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-03 : 19:14:43

quote:
Originally posted by indangasy

I have configured Database Mail and its sending out very well. when i excute the querry, am getting an error
quote:
Originally posted by gbritton

Have you tried specifying the @set_user parameter to a SQL user with permissions to send mail?

That's great! BUt, you didn't answer my question....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-03 : 19:45:52

quote:
Originally posted by indangasy

when i excute the querry, am getting an error

Please post the error message as we can't see your screen.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources