Solved

SQL update trigger that kicks off a job

Posted on 2012-03-28
2
446 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Peformance + mulitple query plans 9 55
How can i get data when i use where clause with group by? 3 35
SQL Server stored proc 2 19
Help Required 3 96
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

770 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