sql - How to create a trigger to send email

Question

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

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.

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

Answer 1

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.

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.

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.

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

Here is the example:

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-en/html/fblangref25-psql-coding.html#fblangref25-psql-postevent

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.

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

• Is it possible to use arguments with firebird events : POST_EVENT 'event_name' + string args?
• 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