473,289 Members | 1,866 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

trigger to run an external program

Hi

Can I trigger an external program to run when a new record is added to
the mysql table?

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?

Nov 23 '05 #1
4 20048
My SQL wrote:
Hi

Can I trigger an external program to run when a new record is added to
the mysql table?
In theory yes, you can write a UDF to do something like this, and call
that UDF from a trigger, but that's not an advisable solution, IMO.

UDF's that try to change state outside the DBMS (e.g. writing files,
starting processes, sending notifications, etc.) are risky. They may
take an unbounded amount of time to execute. Or they may be a security
vulnerability. Or they may have a bug that makes them crash, which
would bring down your MySQL server.
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.


I think it's better to have another process monitor the data for
changes. If you really have a requirement that this be close to
real-time, you need to do continual scanning.

You might consider defining a special table to store the "flag" data
that a change has occurred, and on which record, etc. Then the external
process monitors only that one table. Once the external process has
accounted for a given change, it deletes the flag from that table.

Perhaps you could use the CSV storage engine to make it easier for that
external process to perform the monitoring without burdening the MySQL
engine.
See http://dev.mysql.com/doc/refman/5.0/...ge-engine.html

Regards,
Bill K.
Nov 23 '05 #2
>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
Nov 23 '05 #3
Thanks Bill

I got to know the risks involved. Will this continuous reading cause
any serious delays to the other queries being executed?

Thanks again

Nov 23 '05 #4
My SQL wrote:
I got to know the risks involved. Will this continuous reading cause
any serious delays to the other queries being executed?


Probably not serious delays. I can't answer for certain, because I
don't know your system. The answer depends on many things, including
your database structure, the queries used on it by both readers and
writers, and also your system hardware, etc.

The only way to answer for certain is for you to implement it and test
with the monitoring application on and off.

Regards,
Bill K.
Nov 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: chandan | last post by:
Is it possible to call an external java program from a trigger in oracle 8i. I know that you can call a stored procedure in java from a trigger but i want to know is it possible to call an...
4
by: Joel Thornton | last post by:
Whenever something is inserted to a given table, I want to run some shell commands using xp_cmdshell. Would it be a bad idea to put this xp_cmdshell in the INSERT trigger of this table? I...
6
by: Rolf Kemper | last post by:
Dear All, we are running SQL2000 Sever and make use of the xp_sendmail. For any reason the mail service can run into problems and it looks like that the statemnt below gets not finished. ...
1
by: Simon Holmes | last post by:
Hi, I am having trouble calling a UDF from a 'before update' trigger whereas I have no problems calling it from the 'after update' trigger. The trigger is as below : CREATE TRIGGER foo NO...
0
by: Dave Sisk | last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table. (Note this is an external trigger defined with the ADDPFTRG CL command, not a SQL trigger defined with the CREATE...
0
by: JohnO | last post by:
Thanks to Serge and MarkB for recent tips and suggestions. Ive rolled together a few stored procedures to assist with creating audit triggers automagically. Hope someone finds this as useful as...
1
by: Thierry Missimilly | last post by:
Hi Postgres users, I wonder to know if it is possible to launch a Perl program or a unix comand in a trigger function. I have tried to do that in a C trigger developed with the SPI function....
5
by: Mike Nolan | last post by:
I have a before insert trigger that updates a value in another table. It appears that I cannot depend upon that update having taken place in an after insert trigger on the first table. (The one...
1
by: Gregi | last post by:
Hello, I'm using VS2005 and SQL 2005. Is it possible for a SQL trigger to immediately raise an event in my C# program? Actually it's C++/CLI, but it's easy to translate C# -C++/CLI, so any...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.