January 5, 2011 at 1:55 am
Hi i would like to know abt a trigger possibility,
there is a transactions table with the db. We need to send a mail when a new transaction is added falls under certain conditions.
So we thought to write a trigger.
Kindly help me to start write trigger.
the condition is like
If exists (Select * from Table1 where type = 1)
Begin
<Trigger SP_send_DBmail with the result of <Select id, Name, Value from Table1>>
End
The Select query for the SP_send_DBmail should contain the only details of the new record added
Thanks !!
January 5, 2011 at 3:02 am
This should point you in the right direction:
http://msdn.microsoft.com/en-us/library/ms189799.aspx
Especially look at example B.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 6, 2011 at 5:10 am
Koen (da-zero) (1/5/2011)
This should point you in the right direction:http://msdn.microsoft.com/en-us/library/ms189799.aspx
Especially look at example B.
Hi ! Thank you Koen !
But in that example i dont have an option to have a select query of the newly added record.
Any idea ?
January 6, 2011 at 5:21 am
If you look up the syntax of sp_send_dbmail, you'll learn how to do this.
John
January 6, 2011 at 2:03 pm
Inside the scope of a trigger you have access to two special tables called inserted and deleted.
Since your specification is for inserted rows, you should create a FOR INSERT trigger and query the 'inserted' table. This will contain all the rows inserted by the INSERT statement.
January 6, 2011 at 9:56 pm
Just a friendly suggestion... you should never have a trigger send an email because if the email system is down, the trigger may wait indefinitely for it to come back up so it can commit its implicit transaction.
It's a far better thing (IMHO), to use the trigger to insert a new row into an email staging table and have a separate standalone process run once a minute to sweep the staging table for new rows to email.
--Jeff Moden
January 6, 2011 at 11:30 pm
I'd have to agree with that suggestion vehemently...
Second reason - Your still inside the transaction and locking thise resources. Someone else is waiting!
Edit: I know thats what Jeff said! I'm restating for emphasis!
January 6, 2011 at 11:52 pm
Jeff Moden (1/6/2011)
Just a friendly suggestion... you should never have a trigger send an email because if the email system is down, the trigger may wait indefinitely for it to come back up so it can commit its implicit transaction.It's a far better thing (IMHO), to use the trigger to insert a new row into an email staging table and have a separate standalone process run once a minute to sweep the staging table for new rows to email.
That is an excellent suggestion.
I'm going to remember that. (better write it down)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 6, 2011 at 11:56 pm
@jeff. Starting in SQL Server 2005, database mail is using service broker. That means that the sp_send_dbmail does not actually send the email, it just sends a service broker message with the email information to a service. The trigger does not have to wait for the actual email to be sent.
January 7, 2011 at 6:42 pm
Nils Gustav Stråbø (1/6/2011)
@Jeff. Starting in SQL Server 2005, database mail is using service broker. That means that the sp_send_dbmail does not actually send the email, it just sends a service broker message with the email information to a service. The trigger does not have to wait for the actual email to be sent.
Thanks for the info, Nils. Can you tell me, though, if that service is down, what happens inside the trigger?
--Jeff Moden
January 8, 2011 at 12:39 am
If the receiving service's queue is disabled (the service can't be disabled, only it's queue), the the messages will stay in sys.transmission_queue until the queue is enabled again, and then they will be sent automatically.
I've never seen the service broker objects related to database mail go down (being disabled) before, but I still monitor the status of all service broker queues on all servers in case it happens.
We're using it, and have been using it for over four years, to send 10-20 thousand of misc automatically generated emails a month to employees and our customer and sub-suppliers. They are not sent from triggers, instead they are sent by scheduled jobs. If the SMTP server that sp_send_dbmail is using is down, the mail sending will be automatically retried once an hour for 24 hours (this can be configured).
Not once, for these four years, has it failed. It's been rock solid.
We're also using service broker in triggers (self build) to denormalize a bunch of tables into a table that is full text indexed. There has been some occasions where the destination queue has been disabled due to bugs in my code causing poison messages. All that happens is that the messages are piling up in sys.transmission_queue, the trigger runs as normal, and once I've identified the poison message and fixed the bug in the stored procedure that is receiving and processing these messages, the messages in sys.transmission_queue are automatically send and processed at the destination.
January 8, 2011 at 10:00 am
Nils Gustav Stråbø (1/8/2011)
If the receiving service's queue is disabled (the service can't be disabled, only it's queue), the the messages will stay in sys.transmission_queue until the queue is enabled again, and then they will be sent automatically.I've never seen the service broker objects related to database mail go down (being disabled) before, but I still monitor the status of all service broker queues on all servers in case it happens.
We're using it, and have been using it for over four years, to send 10-20 thousand of misc automatically generated emails a month to employees and our customer and sub-suppliers. They are not sent from triggers, instead they are sent by scheduled jobs. If the SMTP server that sp_send_dbmail is using is down, the mail sending will be automatically retried once an hour for 24 hours (this can be configured).
Not once, for these four years, has it failed. It's been rock solid.
We're also using service broker in triggers (self build) to denormalize a bunch of tables into a table that is full text indexed. There has been some occasions where the destination queue has been disabled due to bugs in my code causing poison messages. All that happens is that the messages are piling up in sys.transmission_queue, the trigger runs as normal, and once I've identified the poison message and fixed the bug in the stored procedure that is receiving and processing these messages, the messages in sys.transmission_queue are automatically send and processed at the destination.
VERY cool, Nils. Thanks for taking the time to write that up. As a bit of a sidebar, this is something that I believe lots of folks would be very interested in... you should write an article on what you good folks did for this.
--Jeff Moden
January 8, 2011 at 11:49 am
Service Broker definitely deserves an article. It seems like a feature that very few people use.
I don't know why so few people uses it, but my guess is that there are quite a lot of new object types that you need to get a grip of; message types, contracts, services, queues, routes, certificates, endpoints, transport security, dialogue security, conversations and so on.
To set up a SB system that simply sends a message to a target and back involves quite a lot of work. And if you're "crazy" enough to try to set up server to server communication... Well, there are so many things that needs to be configured, and it is so hard to debug, so no wonder people avoid it. But once you've understood all the objects and how they interact, it's "quite" straight forward.
The best source for learning Service Broker, in my opinion, is at http://www.rusanu.com. He has written a lot of very good articles and examples on all the different aspects in Service Broker. He was also one of the, if not THE, programmers at Microsoft, that designed Service Broker, so you would expect him to know how it works.
I attended SQLPass this year (well, it's last year now) in Seattle, and there was a technology lunch where you could discuss and talk about different topics related to SQL Server. I attended the Service Broker lunch table, and only I and the "chairman" did actually use Service Broker.
I'd love to write an article about practical use of Service Broker. Using it to denormalize tables, and then full text indexing the denormalized table, is the most useful thing I have done with it. The problem with writing an article is that I don't know where to find the time for it. Working 8 hours a day, and then coming home to take care of two small children, takes most of my time. But who know, perhaps I can squeeze in an hour here and there while I'm at work. SSC has given me, and therefore my employer, so much knowledge, so giving something back would just be fair.
January 9, 2011 at 10:57 pm
Understood on the time thing. If you get a chance, though, I think it would be a great article. Thanks, Nils.
--Jeff Moden
January 10, 2011 at 3:11 pm
Learning new stuff - Always great 🙂
@Nils - I'm sure there is an article of Service Broker just waiting to come out and educate us all. Thanks for the info
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply