We have one requirement in that the business way to run the scheduled job when we insert a record into the custom table.
There is a job which is scheduled to run every Sunday but some time business want to run this package in weekdays when we insert a record into a table (Manual_Job_run_history)
Structure of Manual_Job_run_history table
|
create table Manual_Job_run_history
(RunID int identity(1,1),
Executed_by varchar(50),
Rundate datetime
)
|
With the help of the trigger, we can achieve this task
A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERTING, UPDATE, or DELETE statements on a table or view.
The job is scheduled on the Sql server Agent
In this job calling a simple package.
in this package, I am inserting a record into a table.
Inserting a table into this package
Now I am running this package
Package executed successfully.
See the record into the table
Now I am creating a trigger on the Manual_Job_run_history table on insert event
|
Create trigger trg_Manual_Job_run_history_insert
on Manual_Job_run_history
for insert
AS
BEGIN
EXEC msdb.dbo.sp_start_job N'TestJob'
END
|
The trigger is created.
Now I am inserting a record into this table
Before inserting a record into the Manual_Job_run_history table see the records in the BadData table
Now inserting a record into the table
See job is executed successfully
Record is also inserted into the BadData table
We can see the job execution details in the Job history
Hope it will help.
Thanks!
