SQL Server Trigger -is this possible and the correct usage

Question

i have had a look but was unable to find something specific enough for my answer so wanted to ask the question

firstly, is this possible?

secondly, is my searching not up to scratch and this is already answered - if so where?

lastly, is there an alternate route which is better?

Ok so my scenario is using SQL Server, i have a SQL Job that runs on a schedule - i want to remove this schedule and say "when this job has finished, run again" but instead of the last step = goto step 1 i wanted to do something like...

Create a table with a PK and some other columns to help control. lets say

CREATE TABLE dbo.MyTable 
(Col1_ID INT Primary Key
, Col2_Desc VARCHAR(256),
 Col3_DT SmallDatetime default getutcdate()
,Col4_Duration INT default 60)

and then add a trigger to this table so that when an INSERT is done on this table then trigger runs and if it's an insert with the description (Col2_Desc) "Run" or similar then we execute job "MSSQL JOB NAME"

if we then subsequently add a "STOP" as the Col2_Desc then the trigger will not run the MSSQLJOB for however many minutes the Col4_Duration job states.

I.E

 INSERT INTO dbo.MyTable ('RUN',GETUTCDATE(),0)

Trigger runs and execs MSSQLJOB XYZ

INSERT INTO dbo.MyTable ('STOP',GETUTCDATE(),60)

Trigger runs and waits for the duration in minutes

~ 60 minutes later, trigger runs and executes the MSSQL job again.

And this brings me to the WHY, i have a job that runs on a schedule like mentioned above; this job doesn't take long to run nor it is relatively important (our main data warehouse :D - i'm a BI developer..) but when it's built i want to build it again and again and again by design it's fast but i also want to be able to have "scheduled downtime" without having to go and disable the job itself or make sure everything is in order within the tables it's working with or on. i want the job to essentially stop running after it's current run has finished for a period of time once it has finished its current build run - the last step of the build will be insert into table with trigger

so the goal is, insert a row to say "STOP for 20 minutes " to which i can now make my changes and then forget about it because the job will run again and the MSSQL job will start running again continuously.

I've not exactly got a test environment currently, and i'm always catching myself out with the definition changed because i changed it while it's actually running - ideally i want to automatically pause the build for a period of time with only inserting a row into a table if that makes sense?

in theory, i do believe this is possible. but in terms of MSSQL continuous integration, streamlined deployments etc - i don't know if this is a good idea nor a viable idea.

and help appreciated.

Thanks, Richard.

P.S -the reason for the PK on the table i so i can add to MVC application and with a single click send communications to stakeholders of a deployment coming, the details, insert the row required for the stop duration and then do my deployment without having a multitude of things to do - i can automate it all if i can create some form of trigger like this.

Answer 1

OK so I've done some testing, didn't really have the time to look into this but I've managed to make what i wanted work - although it's not ideal and feel personally it should not be used but just so anyone can find and use if there is a use for it.

it requires you have two jobs and two tables to control these. the main job and the alternate job that called by the main job. and then the main table with the trigger, and the table controlling what's being inserted

I prefer all of the other answers as the initial part of my Q was is there a better way of doing this and yes there is. but was fun doing the testing though!

the idea is the second table that controls what is inserted it only takes the most recent row as the action for what it's going to do "TOP 1 ORDER BY DATE DESC" so potentially this table could get bloated but still that's a working model - you'll need perms to create 2 tables and 2 jobs and a trigger to test. code below

CREATE TABLE dbo.MyTestTable
    (
        My_PK INT Primary Key IDENTITY(1,1)
        ,My_Action VARCHAR(256)
        ,My_DateTime SMALLDATETIME
        ,My_Duration_To_Wait INT
    )

GO

    CREATE OR ALTER TRIGGER RunMyJob  
ON dbo.MyTestTable 
AFTER INSERT   
AS  
IF EXISTS(SELECT * FROM INSERTED WHERE My_Action = 'RUN')
BEGIN
    EXEC msdb.dbo.sp_start_job 'TESTJOB'
END

DECLARE @MyInt INT = (SELECT top 1 My_Duration_To_Wait FROM dbo.MyTestTableAction ORDER BY My_DateTime DESC)
DECLARE @WaitTime VARCHAR(256) = DATEADD(Minute,@MyInt,GETUTCDATE())
DECLARE @MyDelay VARCHAR(8) =  CONVERT(TIME, @WaitTime-GETUTCDATE())


IF EXISTS(SELECT * FROM INSERTED WHERE My_Action = 'STOP')
BEGIN
    PRINT 'WAITING FOR ' + CAST(@WaitTime AS VARCHAR(30)) + ' '
    WAITFOR DELAY @MyDelay
    PRINT 'Running again ' + CAST(GETUTCDATE() AS VARCHAR(256))
    EXEC msdb.dbo.sp_start_job 'TESTJOB'
END
GO   


JOB_NAME = 'TESTJOB'
JOB STEP 1 = SELECT 1
JOB STEP 2 = EXEC msdb.dbo.sp_start_job 'TESTJOBINSERT'

JOB_NAME = 'TESTJOBINSERT'
JOB STEP 1 = 
INSERT INTO dbo.MyTestTable
SELECT TOP 1 My_Action, GETUTCDATE(), My_Duration_To_Wait FROM dbo.MyTestTableAction ORDER BY My_DateTime DESC


CREATE TABLE dbo.MyTestTableAction
    (
        My_Action VARCHAR(256)
        ,My_Duration_To_Wait INT
        ,My_DateTime DATETIME
    )
INSERT INTO dbo.MyTestTableAction VALUES ('RUN',0,GETUTCDATE()) -- just run as normal


INSERT INTO dbo.MyTestTableAction VALUES ('STOP',10,GETUTCDATE()) -- Wait 10 minutes before starting again

Answer 2

I'd recommend something like this:

1. Set the job to run every 30 minutes (or whatever time you want)
2. Add a new first step to the job that checks your table; if the latest record in the table says RUN, then continue the job, otherwise exit the job
3. Add a step to the end of the job that adds a new row with STOP to your table after a RUN is complete

The new first step could like this:

DECLARE @isRun varchar(4) = (SELECT TOP 1 Col2_Desc 
                             FROM yourTable 
                             ORDER BY Col1_ID DESC)

IF @isRun = 'RUN'
BEGIN
SELECT 'Good'
ELSE RAISERROR ('Not time to run', 16, 1)

Set this step to, on failure, complete the job reporting success. On success, continue to step 2.

The new last step would be a simple insert:

INSERT INTO yourTable (Col2_Desc, timestamp) --whatever else
SELECT 'STOP', GETDATE()

This way all you need to do is insert a row to your table with RUN when you want the job to run. You also get your logging of when the job actually completes by looking at the STOP rows.

Answer 3

It's certainly a creative approach, but I'm not aware of any way to tell SQL Server, "wait X minutes from now, and then do Y".

If I were you, I'd look into an approach involving scheduling the job to run every minute (or whatever level of precision you want), and then using your meta table to tell the job whether it's ok to do something or not.