would like to tune the query
I would do the same comments as for the request you've made earlier.
Also you could have a single test for this part :
if((select open_couple from tbl_job_crewprofile where job_id = @JobID) = 0) -- 2 --> only; 1 --> yes; 0 -- no if((select open_couple from tbl_job_crewprofile where job_id = @JobID) = 2)
It seems you could cumulate all tests by specifying LEFT JOINs to the table that handles jobs …
This post confirms there are a couple of rows in the tbl_jobs_test table : this table needs to be indexed. Think of whether it could have a primary key or unique key constraint, as both these are supported by indexes.
The following tables need an index on the user_id column : dbo.tbl_user_passport, dbo.tbl_user_location, dbo.tbl_vrew_objectives, tbl_crew_teamstatus in order to avoid being read each time this batch is executed.
Again, you will need to get rid of the cursor; for this batch it is even more important because the cursor is being executed within a trigger : triggers are executed within the transaction that triggered their execution. So they must be coded to execute the quickest possible way, so as to lower the transaction duration as much as possible : this preserves the access concurrency.