SQL update trigger that kicks off a job

How do you write an update trigger that on update of a record a job get executed?
LVL 1
cheryl9063Asked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
You need to create or change an existing trigger on update to include a command like

exec msdb.dbo.sp_start_job and pass parameters like below:

[dbo].[sp_start_job]
  @job_name    sysname          = NULL,
  @job_id      UNIQUEIDENTIFIER = NULL,
  @error_flag  INT              = 1,    -- Set to 0 to suppress the error from sp_sqlagent_notify if SQLServerAgent is not running
  @server_name sysname          = NULL, -- The specific target server to start the [multi-server] job on
  @step_name   sysname          = NULL, -- The name of the job step to start execution with [for use with a local job only]
  @output_flag INT              = 1     -- Set to 0 to suppress the success message
0
 
lcohanDatabase AnalystCommented:
So it will be something like code below however the SQL login that fires the trigger -or does the UPDATE statement must have sufficient rights to start and run the SQL job:

CREATE TRIGGER [TU_Clients] ON  [dbo].[Clients]
   AFTER UPDATE
AS
BEGIN

-- stop the recursive trigger from firing more than once ...
IF ((SELECT TRIGGER_NESTLEVEL()) > 1 )
BEGIN
   RETURN
END

IF UPDATE(ClientName)
BEGIN
      --start job here
      EXEC msdb.dbo.sp_start_job @job_name = 'Put Your Real Job name here'
            
END
END
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.