来自现有基于 SQL 的应用程序的电子邮件通知
我正在尝试建立在现有定制 CRM 应用程序中使用电子邮件通知的最佳选择。
本质上，我想要做的是，当受影响的用户的一个工作项目违反其 KPI（关键绩效指标）时，向受影响的用户发送一封电子邮件。
我最好是使用 SQL 触发器和存储过程来实现此目的（它们都可以用来实现此目的吗？），还是应该使用一些 C# 编码和计划任务？或者我错过了一条更简单的路线？
I'm trying to establish my best option for using email notifications with an existing bespoke CRM application.
Essentially what I want to do is send an email to an affected user when one of their work items has breached its KPI (key performance indicator).
Am I best to do this by using an SQL trigger and a stored procedure (can they both be used to achieve this?) or should I use some c# coding and a scheduled task? Or am I missing an easier route?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我在使用 Windows 服务之前已经完成了此操作，该服务每分钟轮询数据库，然后撰写并发送电子邮件。
我想触发器是另一种可能的方法，但是如果发送电子邮件失败怎么办？无论如何，您可能想要某种回退，例如计划任务方法。
I've done this before using a windows service which polls the database every minute and then composes and sends emails.
I guess a trigger is another possible approach but what if sending the email fails? You'd probably want some sort of fall back like the scheduled task approach anyway.
您可以使用 Quartz.Net 之类的东西，它是一个调度引擎。它能够按定义的时间间隔/定义的时间运行任务，并且还可以在任务失败时重试，或以某种方式提醒您。
Quartz.Net 可通过 NuGet（或添加库包）获得，并且在其 网站 上提供了大量优秀文档
You could use something like Quartz.Net, which is a scheduling engine. This has the ability to run tasks at defined intervals/at defined times, and also to re-try if a task fails, or alert you in some way.
Quartz.Net is available via NuGet (or Add Library Package) and has lots of good documentation on their site
我个人会使用 SQL 代理作业来撰写电子邮件，然后发送。通过这种方式，您可以为要运行的作业指定某些凭据（例如，访问邮件服务器），并且可以根据需要随时运行它。
我当前使用的方法是一个简单的 CLR 存储过程，它利用 .Net 中的 SMTP 功能，这需要一堆参数来构造电子邮件消息。
I personally would use a SQL Agent job to compose the emails and then dispatch. This way you can specify certain credentials for the job to run (e.g. access to the mail server), and you can have it run as often as you like.
The method I currently use is a simple CLR stored procedure that utilises the SMTP functionality in .Net and this takes a bunch of parameters that construct the email message.
如果您询问在 SQL Server 2005 中发送电子邮件的最佳方式，我认为 [msdb.dbo.sp_send_dbmail] 是您的朋友。您可以在网络上快速搜索以获取详细信息，但在使用它之前您必须正确设置数据库邮件。我将它与 tsqlunit 一起使用来运行夜间作业，以捕获某些业务规则是否符合要求。
相反，如果您正在寻找一种捕获事件以便能够发送电子邮件通知的好方法，则这完全取决于您的设置以及您希望进行此检查的频率。它可能是您附加到现有存储过程的东西，或者您可以使用触发器（如果您的设置可以处理它）。有时最好从小处开始，在吸取一些教训后迁移到更好的地方。
希望这有帮助！
If you're asking the best way to send an email in SQL Server 2005, I think [msdb.dbo.sp_send_dbmail] is your friend. You can do a quick search on the web to get the particulars, but you must have database mail setup correctly before using it. I used it with tsqlunit to run a nightly job to catch if some business rules were in compliance.
If instead you're looking for a good way of catching events to be able to send an email notification, it all depends on your setup and how often you want this check to occur. It could be something you attach to an already existing stored procedure, or you could use a trigger (if your setup can handle it). Sometimes it's better to start out small and migrate to something better after some lessons are learned.
Hope this helps!