on update trigger to cdonts email sp

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 2000 Forums

SQL Server Development (2000)

on update trigger to cdonts email sp

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-17 : 09:08:42

Stacey writes "Working with a web application which I have a trigger that runs on a table which simply changes the status_id field to 3 when the datetime_completed field is updated:

CREATE Trigger CloseRequest ON [Tickets]
FOR UPDATE
AS

If Update(datetime_completed)
Begin
If (SELECT datetime_completed
FROM inserted) Is Not NULL
Begin
Update Tickets
Set status_id = 3
From inserted b, Tickets a
Where a.ticket_id = b.ticket_id
End
End
----------
What I would like to do now is add onto this trigger to execute a sp passing a few fields as variables from this updated record to it - for instance, the cust_email. It is a sp that I found on a sql site that uses cdonts to email via SMTP. The cust_email being the @to. I am at a loss at to how to pass the cust_email value over as the @to variable. Any ideas?

CREATE PROCEDURE SP_CDONTS_NewMail_Send
@to VARCHAR(8000)    
@from VARCHAR(8000)
@subject VARCHAR(8000)
@body VARCHAR (8000)
	
AS

DECLARE @result INT
DECLARE @object INT

PRINT 'Creating the CDONTS.NewMail object'
EXEC @result = sp_OACreate 'CDONTS.NewMail', @object OUTPUT
IF @result <> 0
BEGIN
    PRINT 'sp_OACreate Failed'
    RETURN @result
END

PRINT 'Sending the message using the Send method'
EXEC @result = sp_OAMethod @object, 'Send', NULL, @to, @from, @subject, @body
IF @result <> 0
BEGIN
    PRINT 'sp_OAMethod Failed'
    RETURN @result
END

PRINT 'Destroying the CDONTS.NewMail object'
EXEC @result = sp_OADestroy @object
IF @result <> 0
BEGIN
    PRINT 'sp_OADestroy Failed'
    RETURN @result
END

RETURN 0
GO

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-17 : 13:44:20

Stacey,

You'll run into a problem when more than one record is updated at once. Also, instantiating the CDONTS object is relatively slow, compared to the rest of SQL Processing. You might consider just dropping the values into a queueing table and setup a scheduled job to later create the emails, thereby allowing the trigger to complete faster, and you can do a group insert, so multiple rows is not an issue.

Here's an article that might help: [url]http://www.sqlteam.com/item.asp?ItemID=5908[/url]

-------------------
It's a SQL thing...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources