July 15, 2008 at 3:46 pm
Hi All,
Keeping this simple, I have a table called EmailHistorywith an AutoID, EmailTo (nvarchar (250)), EmailSent (Bit - default of 0). I have a third party application that will write a new record to this table.
I then want a trigger to run a stored procedure I have which use's database mail, I want the trigger to activate when a new record is inserted in the EmailHistory table, passing the variable entered in the EmailTo column into the stored procedure once the data has been passed to the SP the EmailSent column is updated to 1, the SP then does the rest. Please help... Stored Procedure code is as below;
CREATE PROCEDURE
@EmailTo nvarchar(250)
AS
BEGIN
SET NOCOUNT ON;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'VoiceSQLMail',
@recipients = @EmailTo,
@body = 'Testing';
END
July 16, 2008 at 1:41 am
CREATE TRIGGER trg_Insert
ON TableName
AFTER INSERT
AS
BEGIN
DECLARE @EmailTo VARCHAR(255)
SELECT @EmailTo = Inserted.EmailTo FROM Inserted
EXEC @EmailTo
END
July 16, 2008 at 1:57 am
a.thomson2 (7/15/2008)
CREATE PROCEDURE@EmailTo nvarchar(250)
AS
BEGIN
SET NOCOUNT ON;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'VoiceSQLMail',
@recipients = @EmailTo,
@body = 'Testing';
END
is this a typo, your missing the procedure name. I'm assuming @Emailto is the input parameter
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 16, 2008 at 6:07 am
You will need something like this:
[font="Courier New"]CREATE TABLE dbo.EmailHistory
(EmailHistoryID INT NOT NULL IDENTITY(1,1)
, EmailTo VARCHAR(250) NOT NULL)
GO
CREATE PROCEDURE dbo.SendMyMail
@EmailTo VARCHAR(250)
AS
--Send mail here
PRINT 'Mail Sent'
RETURN(0)
GO
CREATE TRIGGER trgEmailHistory_Insert
ON dbo.EmailHistory
AFTER INSERT
AS
BEGIN
DECLARE @EmailTo VARCHAR(255)
DECLARE MailCur CURSOR FOR SELECT EmailTo FROM INSERTED
OPEN MailCur
FETCH NEXT FROM MailCur INTO @EmailTo
WHILE @@Fetch_Status = 0
BEGIN
EXEC dbo.SendMyMail @EmailTo
FETCH NEXT FROM MailCur INTO @EmailTo
END
CLOSE MailCur
DEALLOCATE MailCur
END [/font]
Remember, your trigger could be working on multiple records if more than one record is inserted at a time. This may not be possible by your current application architecture, but you should ALWAYS support it in a trigger. You should not have to mark records with a bit to indicate a message was sent. Unless you disable the trigger, the record cannot be inserted without sending the message to the service broker.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply