Solved

SQL update trigger that kicks off a job

Posted on 2012-03-28
2
444 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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now