Sql-server – how to use trigger to make SQL Job’s step success or fail

sql-server-agenttrigger

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.

Best Answer

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
Related Question