Alternate of trigger in SQL server
You can create a new job step at the end of the existing job that executes the "on-failure" job. You use the procedure sp_start_job to call this "on-failure" job. If the job fails, then have the on-failure action to execute that new job step. For this new step, make sure to set the on-success-action to "Quit the job reporting failure" so you know it failed.