I have a trigger that fires on insert of a record to the table. What I need to do as well is then fire a VB.NET application, but in the process pass the values that have just been inserted into the table to the VB.net program and use those to perform some functions there in.
I am updating SAP using the dot net connector and have a batch process already but it takes up to 6 hours, so hence the requirement to do it for every insert. The 6 hours represents a process time of about 10 seconds per row, and a row will only be entered about every 1 minute. So to capture the data takes about a day and then a batch process most of the night, which means the results can only be verified the following day, not ideal, but if it can be done as each row is inserted then the results can be updated to SAP and verified the same day.
Your batch process, if scheduled through SQL Server Job Scheduler, can run as frequently as once a minute if you want. But if it is taking 10 seconds to process a single row, then you have other issues. Post the code that is taking so long.
You should definitely avoid tying a trigger to an outside application. This is one of the ways triggers are frequently missused. If you have to, use the trigger to populate a staging table and then run a batch process against your staging table periodically.
If it's not practically useful, then it's practically useless.
Thanks for your input. Your idea of running a batch process every few minutes can work except for one thing, the job will only be once a month and not on the same date, so it is dificult in that regard. The time problem is based on our network. We unfortunately do not have the best of lines and have to connect to our servers on the other side of the country, send information, do some proecessing on the server, send the results back and then based on this send more information to the server, there is nothing that can be done with regard to shortening it.
Thank you for your help. I will try and make aplan with regard to executing the batch every few minutes.