Sending e-mail when a condition is met. 1

[StevenK]

I'm looking for the most simple way to handle the following situation.

We have a CUSTOMER table with a CREDIT_LIMIT field and a BALANCE field.
When the BALANCE value goes over the CREDIT_LIMIT (following the creation of a Sales invoice, say) we want a trigger to activate and send a target list of recipients an e-mail indicating that the customers credit limit has been exceeded.
Different customer accounts may have different account managers - and as such different email addresses may be used dependent on the customer in question.

What can I put in place to achieve something like this?

Any advice / pointers would be appreciated.
Thanks in advance.
Steve

 

[Catadmin]

Your options are:

1) Create a DTS package or SSIS package that monitors the column and kicks off an email job when appropriate.

2) Trigger as listed above which could kick off an email job or a stored procedure which sends an SMTP job

Both could be resource intensive and cause performance to suffer. A third option is to build the functionality into your application so it's the app doing the work based on the values returned by SQL Server and not the SQL Server itself.

I'd go with the third option myself.

Hope this helps.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[StevenK]

Can any further advice be given in this regards?
1. How can an email job be triggered via a DTS package?
[I've only done the basics with DTS allowing myself to import / export data between databases - and not dealt with e-mails and the likes]
2. Similarly how could I set up an e-mail action in a trigger or stored procedure?

Are there any useful links I can be directed to in order to detail the basics on these actions for me?

Thanks in advance.
Steve

 

[DocuMan]

If you've got SMTP running on a Windows 2003 server, you can use the following SP. If it's 2000, then use the CDONTS object (some of the properties will be different).

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SendEmail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SendEmail]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[SendEmail]
(
@From varchar(100) = null,
@Sender varchar(100) = null,
@ReplyTo varchar(100) = null,
@To varchar(100),
@CC varchar(100) = null,
@BCC varchar(100) = null,
@Subject varchar(100) = null,
@Body varchar(4000) = null
)

AS

set nocount on

declare @MailID int
declare @hr int

EXEC @hr = sp_OACreate 'CDO.Message', @MailID OUT

if len( @From ) > 0
EXEC @hr = sp_OASetProperty @MailID, 'From', @From
if len( @Sender ) > 0
EXEC @hr = sp_OASetProperty @MailID, 'Sender', @Sender
if len( @ReplyTo ) > 0
EXEC @hr = sp_OASetProperty @MailID, 'ReplyTo', @ReplyTo

EXEC @hr = sp_OASetProperty @MailID, 'To', @To
if len( @BCC ) > 0
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
if len( @CC ) > 0
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC

EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'TextBody', @Body


EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO