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

SQL Server Development (2000)

Email Trigger

Author

Topic

jackstow
Posting Yak Master

160 Posts

Posted - 2001-11-07 : 06:59:54

I have a scipt that sends an email from Sql Server 2000 - it works fine when I run it from query analyzer and it works fine in a job I have set up. However, I'm trying to get it to work as a trigger with no success.
The idea is to have an email sent when an insert takes place. Here is the code - any ideas? -

CREATE TRIGGER email_alert ON table
FOR INSERT
AS

DECLARE @bodytext varchar(1000)
DECLARE @object int
DECLARE @hr int
DECLARE @count int

SELECT @count = COUNT(*) FROM INSERTED

IF @count > 0
BEGIN

--send email
SET @bodytext = 'email alert'

EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
EXEC @hr = sp_OASetProperty @object, 'From', 'email address here'
EXEC @hr = sp_OASetProperty @object, 'Body', @bodytext
EXEC @hr = sp_OASetProperty @object, 'Subject','::: email alert ::::'
EXEC @hr = sp_OASetProperty @object, 'To', 'email address here'
EXEC @hr = sp_OAMethod @object, 'Send', NULL
EXEC @hr = sp_OADestroy @object

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources