March 19, 2009 at 5:06 pm
I am trying to send an email to a person when a checkbox is checked in that row or record. The problem I am having is it sends the email whenever any value in the record or row is changed. I need help.
USE Db
GO
--Remove trigger if exits.
DROP TRIGGER [dml_TriggerEmail]
GO
--Create trigger to fire when Visitor record has the field 'Department Approval' checked.
CREATE TRIGGER dml_TriggerEmail
ON Visit
FOR UPDATE
AS
-- Delcare variable to store changed row
DECLARE @ROWID INT
-- Declare e-mail variables
DECLARE @EmailTo VARCHAR(250)
DECLARE @EmailBody VARCHAR(MAX)
DECLARE @EmailSubject VARCHAR(200)
-- Setup e-mail variables
SET @EmailSubject = 'Email Boss'
This is my area of concern. I think? lol
-- THIS IS NOT WORKING I ONLY WANT TO DO THE REST IF THIS COLUMN ON THAT @ROWID CHANGES
-- AND IT IS EMAILING IF ANY COLUMN IN THAT ROW IS CHANGED???? Hmmm
-- DeptApproval is a BIT ( a checkbox either '1' or '0')
IF UPDATE (DeptApproval)
BEGIN
-- I get the correct record with this statement but I only need this row if the DeptApproval has been change to a '1'
SELECT @ROWID = ID FROM inserted WHERE DeptApproval = '1'
SELECT @EmailTo = Name FROM inserted WHERE ID = @ROWID AND DeptApproval = '1'
-- Create E-mail body for email
SET @EmailBody = ''
SELECT @EmailBody = @EmailBody + CASE ISNULL(Visit.LastName, '')
WHEN '' THEN '' ELSE RTRIM(LTRIM(Visit.LastName)) + ', '
END + CASE ISNULL(Visit.FirstName, '')
WHEN '' THEN '' ELSE RTRIM(LTRIM(Visit.FirstName))
END
FROM Visit
WHERE Visit.[ID] = @ROWID
-- Display message if no records located
IF @EmailBody IS NULL
BEGIN
SET @EmailBody = 'No visitor record related to Escort Name was found.'
END
-- E-mail Visit information
EXEC usp_Emails
'test@test.com', --From email address
@EmailTo,
NULL, -- CC address
NULL, -- BCC address
@EmailSubject,
@EmailBody
END
Drew
March 19, 2009 at 6:38 pm
I'd do this as a JOIN ...
SELECT
@EmailTo = i.Name,
@EmailBody = @EmailBody + CASE ISNULL(v.LastName, '')
WHEN '' THEN '' ELSE RTRIM(LTRIM(v.LastName)) + ', '
END + CASE ISNULL(v.FirstName, '')
WHEN '' THEN '' ELSE RTRIM(LTRIM(v.FirstName))
END
FROM inserted i
JOIN deleted d on i.ID=d.ID and
JOIN Visit v ON i.ID=v.ID
WHERE i.DeptApproval = 1 AND d.DeptApproval=0 -- bit, so no quotes needed
The other 2 points I notice are, first, that you set @EmailBody to an empty string (not NULL) so the test to see if it's NULL can never succeed and, second, that the selection assumes that one value is returned for @Email however many records are updated; you might need to actually use a cursor here.
Derek
March 19, 2009 at 9:58 pm
Duplicate posting. Please do not cross post, it just serves to annoy us.
Please continue at this address: http://www.sqlservercentral.com/Forums/Topic680016-1291-1.aspx
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply