October 1, 2012 at 12:42 pm
I am attempting to send an email notification when the Location or EventDate column has been updated.
I need to include event in the email, how can I retrieve the value from the actual record that was updated?
The trigger is getting stuck in a loop right now – probably because of the where statement in my RS cursor
USE [myDatabase]
GO
ALTER trigger [app].[Trigger_Update_Notification] on [app].[Events]
for update as
IF ( UPDATE (Location) OR UPDATE (EventDate) )
begin
DECLARE @SUBJECT as VARCHAR(50)
DECLARE @BODY AS VARCHAR(15)
DECLARE @TOEMAIL AS VARCHAR(50)
DECLARE @CCEMAIL AS VARCHAR(50)
SELECT @SUBJECT = 'myDatabase EVENT CHANGE'
DECLARE RS CURSOR FOR
SELECT app.mySchema.EmployeeEmail, app.mySchema.EmployeeEmail, @SUBJECT, app.events.event
FROM app.Ambassadors INNER JOIN
app.Match ON app.mySchema.Pernr = app.Match.Pernr INNER JOIN
app.Events ON app.Match.EventID = app.Events.EventID
where ( UPDATE (Location) OR UPDATE (EventDate) )
OPEN RS;
FETCH NEXT FROM RS
INTO @TOEMAIL, @CCEMAIL, @SUBJECT, @BODY
WHILE @@FETCH_STATUS = 0
BEGIN
select @TOEMAIL, @CCEMAIL, @SUBJECT, @BODY
DECLARE @return_value int
EXEC @return_value = sproc_SendNotificationEmail @TOEMAIL, @CCEMAIL, @SUBJECT, @BODY
end
end
October 1, 2012 at 12:51 pm
You are in a loop because you don't have a fetch next in your loop. Also, move the DECLARE @return_value int outside the loop. And you don't need the select, the fetch will populate the variables.
See below.
But you really, really, really shouldn't be sending emails from triggers. You should probably do the update in a stored procedure, then send an email, or email, from your application.
DECLARE @return_value int
FETCH NEXT FROM RS
INTO @TOEMAIL, @CCEMAIL, @SUBJECT, @BODY
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @return_value = sproc_SendNotificationEmail @TOEMAIL, @CCEMAIL, @SUBJECT, @BODY
FETCH NEXT FROM RS
INTO @TOEMAIL, @CCEMAIL, @SUBJECT, @BODYend
end
October 1, 2012 at 12:56 pm
In an update trigger the inserted pseudo-table contains the rows with the new values and the deleted pseudo-table contains the rows with the old values.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2012 at 12:59 pm
Second the motion on sending the e-mail outside the trigger.
If your application can't send the e-mail directly, then I recommend having the trigger insert data into a queue, and have a separate process go through the queue periodically and send the e-mails from that.
Otherwise, you can end up with triggers holding up transactions while e-mails are being sent, which can do very weird things to locks and overall server performance and reliability.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply