?
Solved

SQL update trigger that kicks off a job

Posted on 2012-03-28
2
Medium Priority
?
459 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 40

Accepted Solution

by:
lcohan earned 2000 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 40

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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

571 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