how to use trigger to make SQL Job's step success or fail

Question

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.

Answer 1

If you are storing records in a permanent table, you can make use of a trigger below.

--to check status of inserted record  

create trigger triggername   
on tablename    
for insert     
as    
begin     
if exists(select 1 from inserted)    
--do your logic here    
end       

--to check status of deleted record    

create trigger triggername  
on tablename    
for delete
as    
begin   
if exists( select 1 from deleted)    
--do your logic here to start another process     
end    

Just for your information, inserted and deleted tables can be used to know what records are inserted/deleted as the result of the trigger action, and in spite of how many rows are inserted or deleted, the trigger will fire only once per statement. Further, you can combine both of the above statements into one by just doing like below:

create trigger triggername    
on tablename    
for insert,delete    
as    
if exists(select 1 from inserted)    
begin   
--what you want to do when a record is inserted   
end      

if exists(select 1 from deleted)    
begin    
--what you want to do when a record is deleted    
end    

Answer 2

It is possible that some terminology in the Question is misused, but for the moment I will take the Question at face-value.

The Question describes the following scenario:

1. Start Process A
2. Wait for Process A to complete before proceeding
3. Start Process B

This is the natural operation of a SQL Agent job across multiple Job Steps. Nothing special needs to be done in order to achieve the behavior described above: no status table, no insert or delete of a "running" entry in the status table, no loop to check the status of the table, and no trigger to possibly replace the loop that wasn't needed to begin with.

And the default operation of each Job Step is to cancel the entire Job if an error occurs (meaning: do not proceed to the next Job Step), though this is configurable.

And this does not mean that you can't have a log table where you track the start and end times of the job over X days. You can certainly have an additional table that would either be just informational or perhaps used to determine whether or not the process ever completed successfully and if it needs to do any more work. For example, I have created multiple-step jobs that track their progress into a Log or Status table. This allows me to schedule the Job to run every 5 or 10 minutes (or whatever is appropriate for the particular operation) and each Job Step will check the current status at the beginning and skip to the next Step if the current Step has already completed. This way the Job does not needlessly repeat work / processing that finished successfully.