I guess the answer is "it depends".
But there's one thing I wouldn't do: using sp_send_dbmail directly within a trigger, since the mail sending process would become part of the insert transaction which may have a significant impact on performance (especially if your "primary application" performs row-based inserts).
Do you need immediate email alert or would a scheduled email be enough?
Do you need details per related insert statement or a summary?
Depending on the answer to the questions above I'd either use Service Broker. Or a "message table" filled by insert triggers for the tables in question together with a scheduled job to send me the summary.
Side note: Service Broker is an interesting feature worth to get used to and I don't think it would be "overkill" for that task. It's better to start with a rather easy task to see how it works rather than waiting for a much more complicated scenario...
Thanks for replying; I was kind of suprised there was only 1 reposne for this kind of inquiry.
I'm not going to call thge MSDB directlry from within a trigger but from a SQL Job. The Trigger will poopulate the table that serves as the messages queue which the SQL Jjob will check on a specified incriment. I got the idea for doing it this was several months back from another lon time SQL Server Central memeber Jack Corbett. In that post I explained problems I arn into with permissions and using that MSDB stored procedure and Jack suggested a message queue setup similiar to what I described.
I have almost no experience with Service Broker and when I went to read about in hopes of possibly using it I was very much discouraged by what looked like apurposefully confusing setup. It may be that this allows for a great level of control with SB (Service Broker) down the road on more complex uises of it but for a simple message queue or alert system it seemed like a look of kludge to me. Its very similiar to SSIS when compared to DTS. While SSIS is clearly extremly powerful and flexable its also very much over done when you want to do something simple or at least that was siple in DTS.
As for your questons...Do you need immediate email alert or would a scheduled email be enough?
Fopr tyhe most part know as the reason for this to begin with is FYI and BTW messages. For example one would be to alert a member of our accoutning team that a Vendor was setup with a key piece of data missing. because this key data is not always required it is left optional and so when a new vendor is setup it would help a lot (especially come the end of the year when you do Vendor 1099's for the IRS) if the head accountant could catch at that time when a vendor was created who did not have this key info and that should have gotten it.
I however would also to eventaully have the ability for handling more urgent messages. This is for a database used by our primary accounting software system and so we can't just change the DB's schema and so we have to work around its design. And so to that end there are scenarios where the normal approach is not feasible because we can't just alter the DB Schema (short of adding in our own passive objects) and risk loosing our support on the peoduct.Do you need details per related insert statement or a summary?
Sometimes we woudl and at others a short subject line tyupe of message is enough. I ahve a base table setup that can store all message specific info I would use. I however don;t have the SQL Job or ther objkects in place yet.
SERVICE BROOKER NOTE: While I'm not favorable of SB right now that doesn't mean I wouldn't like to learn more so if you ahve any links/references on SB I'd love to check them out.
Kindest Regards,Just say No to Facebook!