>Can I trigger an external program to run when a new record is added to
the mysql table?
This is awkward, and tends to have problems if the external program
can fail.
The external program is in C and instead of scanning the table
continuously for new insertions, it will be better if an external
program could be triggered.
Any suggestions?
One approach is to have a new table: work_to_do. A trigger inserts
a record into this table with a date stamp and identification of
the record that changed (and perhaps WHAT changed or the old value
of the field) along with a "job number". You have a process look
at work_to_do occasionally and process the changes. Since there's
a date in there, you can do the changes in order.
If the external program succeeds, delete the record from work_to_do
(by "job number"). Note that you could have several changes for
the same record before the external program gets to it, or perhaps
while the external program is handling some of the changes, another
one comes in. If the external program *FAILS*, leave the record
in work_to_do. Whether you proceed with independent changes after
a failed change is up to you.
This sort of thing is common when you've got a central billing
system and a distributed mail system, so when a mail account is
created, the mailbox has to be created on one of the mail servers.
If the mail server is down for maintenance at the moment, it's
important that the mailbox creation be done eventually.
Gordon L. Burditt
