March 7, 2012 at 2:43 pm
I am using the following code to send an email to the customer, whenever the customer has an update on the f_name.
I want to send the body of the email as the fields which have been affected.
Like if a customer has f_name as'john' and it has been changed to 'james', The email should go as
Customer with ID= 1234 has been updated with previous First Name is 'John' and the new First Name is 'James'
--drop trigger CustomerUpdateMail
CREATE TRIGGER CustomerUpdateMail
ON customer_info
FOR UPDATE
AS
declare @CustomerID int;
declare @message nvarchar(2000);
declare @CustomerName nvarchar(50);
declare @CustomerNewName nvarchar(50);
SELECT @CustomerID = cust_id, @CustomerName = d.f_name FROM deleted d;
SELECT @CustomerNewName = f_name FROM inserted;
SET @message = 'Customer with ID= @CustomerID has been updated
with previous First Name is @CustomerName
and the new First Name is @CustomerNewName '
EXEC msdb..sp_send_dbmail @profile_name='test_mail',
@recipients ='test@exam.com',@subject = 'Customer Information Updated',
@body = @message;
Please help.
March 7, 2012 at 3:08 pm
Need a little more clarification on what you are trying to do but in general your approach to this trigger is not going to work well. In your code you assume there is only 1 record in inserted which just isn't to be the case.
It looks like you want to send an email everytime a row is updated?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2012 at 3:11 pm
It's not a good design to email from a trigger. You're killing performance of update. If I would be MS i would not allow to call xp_sendmail from a trigger at all. Had to deal with such implementation few times...
You should consider other ways:
1. Trigger inserts into some kind of "changes to email" table (usually called Audit table).
2. Regular scheduled job or Service polls the table and sends email out.
March 7, 2012 at 3:27 pm
Sean Lange (3/7/2012)
Need a little more clarification on what you are trying to do but in general your approach to this trigger is not going to work well. In your code you assume there is only 1 record in inserted which just isn't to be the case.It looks like you want to send an email everytime a row is updated?
Ya
I need to send an email to the customer whenever update occurs.
Thanks
March 8, 2012 at 3:20 am
Beginner_2008 (3/7/2012)
...Ya
I need to send an email to the customer whenever update occurs.
Thanks
Again, don't do it in a trigger.
Do you have to report update which happen via some particular channel? If yes, you can email as part of update process (stored proc).
Otherwise, use service or scheduled job to email.
March 8, 2012 at 7:01 am
Eugene Elutin (3/7/2012)
It's not a good design to email from a trigger. You're killing performance of update. If I would be MS i would not allow to call xp_sendmail from a trigger at all. Had to deal with such implementation few times...You should consider other ways:
1. Trigger inserts into some kind of "changes to email" table (usually called Audit table).
2. Regular scheduled job or Service polls the table and sends email out.
Actually, if I'm not mistaken, this process you've mentioned is pretty much what the OP's
code will do... ie. send the mail items asynchronously as it appears he is using Database Mail which I believe uses Service Broker.
You're right though, probably not the best design approach.
March 8, 2012 at 7:28 am
If they do not have to be sent instantly I would recommend the approach of using an audit table and scheduling a job to do this at some regular interval.
If it has to be instant it is kind of a nasty situation. As I explained in my first post you are going to have to process every row and not just assume your inserted table has a single row. That means some sort of RBAR construct will have to be done.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2012 at 7:39 am
OTF (3/8/2012)
Eugene Elutin (3/7/2012)
It's not a good design to email from a trigger. You're killing performance of update. If I would be MS i would not allow to call xp_sendmail from a trigger at all. Had to deal with such implementation few times...You should consider other ways:
1. Trigger inserts into some kind of "changes to email" table (usually called Audit table).
2. Regular scheduled job or Service polls the table and sends email out.
Actually, if I'm not mistaken, this process you've mentioned is pretty much what the OP's
code will do... ie. send the mail items asynchronously as it appears he is using Database Mail which I believe uses Service Broker.
You're right though, probably not the best design approach.
You are rigth in terms that an email will be sent asynchronously, however enqueueing operation (to get MessageId back) is synchronous and it's slow enough to avoid doing so in a trigger.
March 8, 2012 at 8:16 am
Thanks for the suggestions.
Can anybody please provide the best alternative with an example? I am new to Database mail.
March 8, 2012 at 8:30 am
Do you want to send email only if Customer FirstName is changed?
What about Lastname?
What about other details?
If yes to any of the above two, what you email when few details changed at once?
You may want to have a look about CDC in SQLServer 2008
March 8, 2012 at 8:33 am
Beginner_2008 (3/8/2012)
Thanks for the suggestions.Can anybody please provide the best alternative with an example? I am new to Database mail.
Here is a good place to start. http://msdn.microsoft.com/en-us/library/ms175887.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2012 at 8:50 am
Eugene Elutin (3/8/2012)
OTF (3/8/2012)
Eugene Elutin (3/7/2012)
It's not a good design to email from a trigger. You're killing performance of update. If I would be MS i would not allow to call xp_sendmail from a trigger at all. Had to deal with such implementation few times...You should consider other ways:
1. Trigger inserts into some kind of "changes to email" table (usually called Audit table).
2. Regular scheduled job or Service polls the table and sends email out.
Actually, if I'm not mistaken, this process you've mentioned is pretty much what the OP's
code will do... ie. send the mail items asynchronously as it appears he is using Database Mail which I believe uses Service Broker.
You're right though, probably not the best design approach.
You are rigth in terms that an email will be sent asynchronously, however enqueueing operation (to get MessageId back) is synchronous and it's slow enough to avoid doing so in a trigger.
Indeed 🙂
March 8, 2012 at 8:57 am
Sean Lange (3/8/2012)
Beginner_2008 (3/8/2012)
Thanks for the suggestions.Can anybody please provide the best alternative with an example? I am new to Database mail.
Here is a good place to start. http://msdn.microsoft.com/en-us/library/ms175887.aspx
Any example for the case above is appreciated. I am able to implement it but I am not getting dynamicaly passed values in the trigger.
I will try to later implement CDC 2008, For now I want to use this trigger. I just had the code posted for making reference but i am trying the different column to be updated in my actual code. Please suggest.
March 8, 2012 at 9:05 am
Beginner_2008 (3/8/2012)
Sean Lange (3/8/2012)
Beginner_2008 (3/8/2012)
Thanks for the suggestions.Can anybody please provide the best alternative with an example? I am new to Database mail.
Here is a good place to start. http://msdn.microsoft.com/en-us/library/ms175887.aspx
Any example for the case above is appreciated. I am able to implement it but I am not getting dynamicaly passed values in the trigger.
I will try to later implement CDC 2008, For now I want to use this trigger. I just had the code posted for making reference but i am trying the different column to be updated in my actual code. Please suggest.
This will explain it far better than I can. http://msdn.microsoft.com/en-us/library/ms190307.aspx
Before you use this trigger you HAVE to make sure you can handle multiple row updates. At some point you are going to have to call this proc for each row that is updated.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2012 at 9:10 am
Eugene Elutin (3/8/2012)
Do you want to send email only if Customer FirstName is changed?What about Lastname?
What about other details?
If yes to any of the above two, what you email when few details changed at once?
You may want to have a look about CDC in SQLServer 2008
I want to send email whenever a field is changed for the particular cutomerid. For example, In the above code I gave, when the first name is altered it should send an email. I just want to send email whenever that particular filed is updated.
CDC is a good feature in sql server 2008 R2.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply