如何创建触发器以发送电子邮件 [英] How to create a trigger to send email

问题描述

我正在尝试在SQL中创建一个触发器，以将电子邮件发送到特定地址.大纲是:

I am trying to create a trigger in SQL to send email to specific addresses. The outline is:

1. 我已经创建了一个 select ，它返回一个警报列，指示余额低时为1，否则为0.
2. 我需要创建一个 trigger ，如果警报列为1，它将发送一封电子邮件.
3. select 和 trigger 每周需要执行一次.

1. I have created a select that returns an alert column indicating 1 if balance is low or 0 if it is not.
2. I need to create a trigger that if alert column is 1 it sends an email.
3. The select and trigger need to be executed once a week.

实现此目标的最佳方法是什么?任何准则都将有所帮助.我正在使用Firebird数据库.

What could be the best way to accomplish this? Any guidelines would be helpful. I am using a Firebird database.

推荐答案

尽管数据库引擎可以发起发送电子邮件，但绝不可以执行发送电子邮件.在数据库服务器内只能执行一些简短(快速)且无错误的操作.

While the database engine may initiate e-mail sending it should never do the sending itself. Only some short (quick) and error-free actions should be done within the database server.

应该有另一个正在运行的应用程序/服务/守护程序，该应用程序应该基于SQL领域中准备的数据来发送电子邮件.问题是何时应触发该发件人应用程序.

There should be another application/service/daemon running, which should be doing the e-mails based on the data prepared in SQL realm. The question is when this sender application should be triggered.

与数据库无关的方式是按计划的时间间隔轮询数据库，该时间间隔在传统上被昵称为"cron"动作，由Rajiv Shah提到.每分钟一次或每秒一次或每小时一次-由您选择.

The database-agnostic way would be polling the database by scheduled time intervals, which traditionally nicknamed as "cron" actions, mentioned by Rajiv Shah. Once a minute or once a second or once an hour - by your choice.

一种特定于Firebird的方式将使用 POST_EVENT< string constant> 命令.可以代替基于时间的轮询来使用它.

A Firebird-specific way would be using POST_EVENT <string constant> command. It can be used instead of time-based polling or together with.

这里是示例:

CREATE TRIGGER POST_NEW_ORDER FOR SALES
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
  POST_EVENT 'new_order';
END

https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-zh-CN/html/fblangref25-psql-coding.html#fblangref25-psql-postevent

您的程序如何订阅这些事件将取决于编程语言和Firebird访问库.据我了解，PHP可能不太适合那里，因为它更适合根据守护程序的请求运行短脚本，而不是作为一个连续运行的守护程序本身.尽管也许PHP专家可以同时使用这两种方法.我也不知道PHP是否支持Firebird事件，这似乎不是脚本语言的优先事项.

How your program would subscribe to those events would be dependent upon programming language and Firebird-accessing library. As far as i understand PHP would probably fit poorly there, as it is more tailored to run short scripts by daemons' requests, rather than being a continuously running daemon itself. Though perhaps PHP gurus can have it both ways. I also don't know if PHP has support for the Firebird events, it does not seem to be a priority for scripting language.

您可以通过搜索 Firebird POST_EVENT 来了解有关事件的更多信息，例如，许多链接如下:

You can read more about Events by googling for Firebird POST_EVENT, among many links there will be for example those:

• 是否可以在firebird事件中使用参数:POST_EVENT'event_name'+字符串参数?
• https://mikejustin.wordpress.com/2012/11/06/firebird-database-events-and-message-oriented-middleware/
• https://www.firebirdsql.org/file/documentation/papers_presentations/Power_Firebird_events.pdf

这篇关于如何创建触发器以发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！