March 19, 2009 at 4:45 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'
:w00t: 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 8:38 pm
The problem you have is that the UPDATE function returns true if the column is mentioned in the INSERT or UPDATE statement that caused the trigger to fire regardless of whether the value changed.
Hence something like "UPDATE T1 SET C1 = C1" whilst actually making no change to the underlying data, will cause your code to execute.
If you need to check whether the value in a column actually changed, the COLUMNS_UPDATED function is the on to use. Have a read about it in Books Online - the examples look pretty good to me.
An observation about the way you coded your trigger is that it will not handle updates or inserts that affect more than one row. Unless you can guarentee that this will never occur, you should update your code to handle multiple rows.
March 19, 2009 at 8:50 pm
No, COLUMNS_UPDATED works the same way, it just combines all of the columns into a single bit pattern. MS cannot give us a function like you want because there may be many rows changed by this one UPDATE statement, and a particular column's value may have changed in only some of them.
What you need to do is to compare the column values from the deleted table to the inserted table.
[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]
March 19, 2009 at 9:21 pm
Here is how I would do it (untested):
CREATE TRIGGER dml_TriggerEmail ON Visit
FOR UPDATE
AS
-- Setup e-mail variables
DECLARE @EmailSubject VARCHAR(200)
SET @EmailSubject = 'Email Boss'
IF UPDATE (DeptApproval)
BEGIN--The column was SET in the UPDATE Command:
DECLARE @sql as NVarchar(MAX)
SET @sql = ''
WITH cteInserted as (
Select *
, CASE ISNULL(LastName, '')
WHEN '' THEN '' ELSE RTRIM(LTRIM(LastName)) + ', ' END
+CASE ISNULL(FirstName, '')
WHEN '' THEN '' ELSE RTRIM(LTRIM(FirstName)) END
as EmailBody
From inserted )
Select @sql = @sql + '
EXEC usp_Emails
''test@test.com'', --From email address
'''+i.Name+''',
NULL, -- CC address
NULL, -- BCC address
'''+@EmailSubject+''',
'''+@EmailBody+''';
'
From cteInserted i
Join Deleted d ON i.YourPrimaryKeys = d.YourPrimaryKeys
Where i.DeptApproval = '1'
And Coalesce(d.DeptApproval, '0') <> '1'
IF @sql <> '' Then EXEC(@sql)
END
[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]
March 20, 2009 at 3:27 am
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 -- check it actually changed
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 if there are multiple rows.
Derek
March 20, 2009 at 11:23 am
Thanks Derrick!
Thnank Barry!
That works but when I update any other column in that row still executes the email procedure which is fine because the @emailTo is null.
New problem. It won't always save the other columns in that row? Wierd huh?
Its like the trigger messes with the other Stored procedures on the Visit table.
Any Ideas to isolate this to just the one field?
Drew
March 20, 2009 at 12:15 pm
Show us what you are actually using now.
Are there any other triggers on the table?
[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]
March 20, 2009 at 5:56 pm
I am actually using it like Derrick has it. It works for that checkbox no problem!
When I make normal changes to my Visit table (Table I am checking to see if column and row changed on).
ie. name or address it will not always update it???
But the email and all works great.
Is the trigger getting stuck? Should I use the recomplie sp?
Once the trigger fires should it be removed and the tables update command refreshed?
Thanks
Drew
Drew
March 23, 2009 at 4:10 am
drewsx2 (3/20/2009)
Thanks Derrick!Thnank Barry!
That works but when I update any other column in that row still executes the email procedure which is fine because the @emailTo is null.
New problem. It won't always save the other columns in that row? Wierd huh?
Its like the trigger messes with the other Stored procedures on the Visit table.
Any Ideas to isolate this to just the one field?
The query I wrote (and didn't test) will only select a mail address when DeptApproval changes from 0 to 1. If you change other fields, the trigger fires anyway, but, as you say, the NULL email address should mean it doesn't try to send email.
The trigger shouldn't affect what actually gets updated. Can you post what you are using now?
Derek
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply