June 2, 2008 at 2:10 pm
I want to send an email using a trigger whenever a Customer has 1 of 3 fields updated. The table is not updated that frequently so I don't mind using a trigger. I don't think I understand the syntax in BOL. Can someone help me please? I know how to send an email when a new Customer is added to the table, but the syntax for the Update is escaping me.
Edit: I almost forgot, I want to put the old and new values into the email so that it would read something like this: "Customer AAA status has changed from (old value) to (new value)."
June 2, 2008 at 4:01 pm
Hope this will helpful for you to find a better solution.
Use COLUMNS_UPDATED() to check whether perticular column has updated or not.
You can use ‘deleted’ virtual table to capture old values and ‘inserted’ table to capture new values.
Looks for BOL for more details.
Susantha
June 2, 2008 at 4:10 pm
I was using BOL as a basis for my syntax, but it's not clicking for me. I don't understand what BOL is trying to say.
June 2, 2008 at 4:28 pm
Lets assume if you want to check whether any of the columns 2,3,4 or all the columns have been updated or not. Then use the following IF condition
IF (COLUMNS_UPDATED() & 14) > 0
14 is the bitmask for all three columns.
for col 2, (this is col id you can see in syscolumns table) bitmask is power(2,(2-1))=2
for col 3, bitmask is power(2,(3-1))=4
for col 4, bitmask is power(2,(4-1))=8
In above ex, since your checking all there columns bitmask is the sum of all. That comes to 14.
Let me know if you need more help.
Susantha
June 2, 2008 at 9:11 pm
I do the trigger part like this. Not sure about the email.
CREATE trigger [dbo].[update_trigger] on [dbo].[MyTable] for UPDATE
as
if
UPDATE
(Field1) or
update
(Field2)
begin
insert into trigger_audit_table
(
Field1,
Field2,
Field3,
Field4,
Field5,
Field6,
Field7,
Field1_Before,
Field2_Before,
AUDIT_TYPE,
AUDIT_DATE
)
select
Ins.Field1,
Ins.Field2,
Ins.Field3,
Ins.Field4,
Ins.Field5,
Ins.Field6,
Ins.Field7,
Del.Field1,
Del.Field1,
'UPDATE',
getdate()
from inserted Ins inner join deleted Del
on Ins.KeyField = Del.KeyField
where Ins.Field1 <> Del.Field2
or Ins.Field2 < 0
end
June 3, 2008 at 2:08 pm
Hey homebrew, that works pretty good. I also got the email to work when I check for 1 field, but not for multiple fields. But what if I'm checking 2 separate fields for change and would send 2 different emails? Do I have to write 2 separate triggers? I would think that I could put an 'OR' statement in there, like this (it looks lengthy, but skip over the joins and get down to the 'IF' statement):
CREATE TRIGGER tr_MyTrigger
ON MyTable
AFTER UPDATE
AS
DECLARE @DealID INT
DECLARE @Deal VARCHAR(40)
DECLARE @oldStatus VARCHAR(10)
DECLARE @newStatus VARCHAR(10)
DECLARE @oldEmployee VARCHAR(30)
DECLARE @newEmployee VARCHAR(30)
DECLARE @textStatus VARCHAR(250)
DECLARE @textEmployee VARHCAR(250)
SET @DealID = (SELECT (DealID) FROM Deleted)
SET @Deal = (SELECT (Deal) FROM MyTable WHERE DealID = @DealID)
SET @oldStatus = (SELECT DS.DealStatus FROM Deleted D INNER JOIN DealStatuses DS ON DS.DealStatusID = D.DealStatusID)
SET @newStatus = (SELECT DS.DealStatus FROM Inserted I INNER JOIN DealStatuses DS ON DS.DealStatusID = I.DealStatusID)
SET @oldEmployee = (SELECT E.Employee FROM Deleted D INNER JOIN vwEmployeesALL E ON E.EmployeeID = D.EmployeeID)
SET @newEmployee = (SELECT E.Employee FROM Inserted I INNER JOIN vwEmployeesALL E ON E.EmployeeID = I.EmployeeID)
SET @textStatus = 'Deal ' + @Deal + ' has been changed from ' + @oldStatus +' status to ' + @newStatus + ' status.'
SET @textEmployee = 'Deal ' + @Deal + ' has been moved from ' + @oldEmployee +' to ' + @newEmployee
IF (UPDATE (DealStatusID))
EXEC msdb.dbo.sp_send_dbmail @recipients='emailaddress',
@subject = @textStatus,
@body_format = 'HTML'
OR
(UPDATE (ClosingEmployeeID))
EXEC msdb.dbo.sp_send_dbmail @recipients='emailaddress',
@subject = @textEmployee,
@body_format = 'HTML'
June 3, 2008 at 2:22 pm
You essentially want to write those as two separate IF statements. Depending on whether you want someone to get just one e-mail, or possibly several if the row had both columns updated, you'd then add an ELSE or not.
So - if you want them to get 2 emails if BOTH columns were updated then :
...
IF (UPDATE (DealStatusID))
EXEC msdb.dbo.sp_send_dbmail @recipients='emailaddress',
@subject = @textStatus,
@body_format = 'HTML'
IF (UPDATE (ClosingEmployeeID))
EXEC msdb.dbo.sp_send_dbmail @recipients='emailaddress',
@subject = @textEmployee,
@body_format = 'HTML'
if you only want them to get just one, then:
IF (UPDATE (DealStatusID))
EXEC msdb.dbo.sp_send_dbmail @recipients='emailaddress',
@subject = @textStatus,
@body_format = 'HTML'
ELSE
IF (UPDATE (ClosingEmployeeID))
EXEC msdb.dbo.sp_send_dbmail @recipients='emailaddress',
@subject = @textEmployee,
@body_format = 'HTML'
You could also change the second one around by combining into a single IF with a CASE statement, but I don't think it buys you anything and is less readable IMO.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 3, 2008 at 2:52 pm
Thanks Matt. Of course 10 minutes after I posted this, I figured out the multiple IF statement solution. Sometimes I feel like such a dork.
June 3, 2008 at 3:09 pm
SQLWannabe (6/3/2008)
Thanks Matt. Of course 10 minutes after I posted this, I figured out the multiple IF statement solution. Sometimes I feel like such a dork.
Welcome to the club !
June 3, 2008 at 3:26 pm
sometimes is good to warn people about "sending emails from triggers may not be what you want" 😉
* Noel
June 4, 2008 at 9:08 am
SQLWannabe (6/3/2008)
Thanks Matt. Of course 10 minutes after I posted this, I figured out the multiple IF statement solution. Sometimes I feel like such a dork.
I get to experience that feeling several times a day..:D
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 5, 2008 at 3:16 pm
noeld (6/3/2008)
sometimes is good to warn people about "sending emails from triggers may not be what you want" 😉
Yes - I really think they should be warned ....
You could really screw performance up by sending emails directly from a trigger - much better to use the trigger to just add a row to some "data to email" table - which would have something like a PROCESSEDDATE field in as well - then all the rows could be processed with a scheduled job (scheduled as often as you need) that goes through the rows in the "data to email" table and performs the emails then sets PROCESSEDDATE to GetDate() - that way you don't trash the realtime performance of your system, can properly recover from email outages (i.e. if the emails fail to send just don't set PROCESSEDDATE etc etc ... also with a bit more thought where two or more fields have changed you can make it send the info of all the changes for the same email address in one email - keeping down the amount of emails you balst out - and I am sure anybody who receives email would appreciate this consolidation as it helps keep that inbox slim
James Horsley
Workflow Consulting Limited
June 6, 2008 at 1:39 am
Right. And there is more. Such trigger would fail on multirow updates, so for proper work you'd have to open a cursor.
If the trigger instead of sending emails just inserts into queue table, you have a simple set based solution instead of cursor.
June 6, 2008 at 10:15 pm
Hence my reasoning for stating in my original post that this is not a table that will get inserted or updated very often.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply