Solved

SQL update trigger that kicks off a job

Posted on 2012-03-28
2
450 Views
Last Modified: 2012-03-28
How do you write an update trigger that on update of a record a job get executed?
0
Comment
Question by:cheryl9063
  • 2
2 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 37778335
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
 
LVL 39

Expert Comment

by:lcohan
ID: 37778355
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question