0

I am trying to execute a Batch File in the trigger in MySQL. Whenever data is inserted in a table Insert On Trigger should be invoked and it should execute/Call the Batch file which is given in Trigger. Is this Scenario possible in MySQL? I did this in SQL Server. In SQL Server it is possible.

CDspace
  • 2,639
  • 18
  • 30
  • 36
Vishwam Pula
  • 37
  • 6

2 Answers2

1

By default, it is not possible to run external commands (start external processes) in MySQL queries or stored procedures.

However, you can use user defined functions (UDF) for the purpose. With UDF, you can write a custom function in C that then you can use in any query or stored procedure. Such a function can obviously also run external processes, with a command line potentially received as a parameter.

Fortunately, this is already implemented, but you have to install it. You can search on http://www.mysqludf.org/, for example this one will allow you to execute OS commands.

Note though, that this may introduce a serious security risk. If there is a SQL Injection vulnerability in your application, having these UDFs loaded greatly increase the risk of a full server compromise (as opposed to just a full application compromise).

Gabor Lengyel
  • 14,129
  • 4
  • 32
  • 59
1

An alternative approach to UDF could be to populate a separate table (maybe in-memory) with an "after insert trigger" and then poll the table periodically with a cron job, invoking your script for every row. Your script will be also responsible of removing the row after the operation is completed successfully.

As an upside the script can run on a different machine than the db one.

It is usually a good practice to keep triggers implementation as simple as possible because it can drastically impact the performance of every insert.

You can also take a look at this question or this FAQ page on MySQL triggers.

Community
  • 1
  • 1
Giorgio Ruffa
  • 456
  • 3
  • 7
  • 1
    While this may work well in many situations, using a relational database as a job queue has its caveats, as described [here](http://softwareengineering.stackexchange.com/questions/231410/why-database-as-queue-so-bad), or [here](https://blog.engineyard.com/2011/5-subtle-ways-youre-using-mysql-as-a-queue-and-why-itll-bite-you) or [here](http://mikehadlow.blogspot.hu/2012/04/database-as-queue-anti-pattern.html). I'm still upvoting your answer, because it is a real option, and can be a good one in a lot of scenarios. :) – Gabor Lengyel Oct 28 '16 at 12:51
  • Thank you @lengyelg, the Percona article is really interesting. I think it's a feasible solution when the job you want to perform it's simple (like sending a digest email), but for an incredible set of reasons you do not want to integrate it in the application logic. – Giorgio Ruffa Oct 28 '16 at 13:11