Use Trigger/Procedure for long program?

[GPhilipp]

I am relatively new to MySQL and have a behavior or structural question.

I am in the process of writing a php website with a MySQL db layer. Here is the scenario:

User enters some data which needs to start a routine based on the data entered and can run from just a view seconds to hours. This routine needs to update some records in the same table and in other tables. This routine also needs to run separately from the web-user who entered the data. There are other users who may also enter data and need to start that routine (or at least check if the routine is running already).

I was thinking to have php update the table, therefore start a trigger (after). But I don't think the long running trigger will allow other modifications to the table (not the same record).

Then I was thinking that the trigger could call a stored procedure, but I think a proc is just an extension of the trigger and would cause the same effect.

A windows scheduler or a unix cron job would do it by checking the status of that table, but i don't like that because i don't trust that solution too much and it would be slower.

What is the correct way of doing this? Is any of the above mentioned ways the correct way? If yes, could you please elaborate on how the architecture of MySQL handles that specifically in regards with the user maybe disconnecting and the routing will still run.

Thank you for your help in advance!

 

[DonQuichote]

Why don't you trust a cron job? In my opinion, it is the safest way to go. First, your database does not do any "automagic" and therefore unexpected changes, and next a cron job is more easily stopped than a loop of triggers. You can also start the job called from cron by hand for testing and checking how long it takes. And, a cron job gives to the opportunity to log errors.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)