Step 1. Call a script to trigger application process. When the application process start, it will insert a record to a specific table. And once the process ends, that record will be deleted from table. The insert and delete SQL is issued from application.
Step 2. Check the running status of step 1. Since there is record when the process is running. Currently, I use two SELECT statement to check. When first SELECT statement gets the record, then the process is started. And then delay about 15 minutes, run another SELECT statement, when the record is deleted. I know the process is finished. Once process is finished, I will trigger step 3 to run another process.
Step 2's method has drawback. Because step 1's process's time will vary from time to time. So I need to adjust the delay time. I think trigger is a better way in this case. But I do not know how to link the trigger with SQL Job Step's success and fail. Would you please provide an example? Thanks.