Run an executable when a specific table updated

Hello,

I like to use the following query to be run every 2 seconds. The query check the last user update for ORDR table every 2 seconds if returns a result then run the executable.

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update --,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'LiveDB')
AND OBJECT_ID=OBJECT_ID('ORDR') and CONVERT(VARCHAR, last_user_update, 120) >= dateadd(S,-2,getdate()) 

The executable name and location is:
\\SQL\app\InsertRows.exe

Please let me know if there are better ways to accomplish the same goal.

Note: I don't want to use trigger for this particular case.

Thanks for any help in advance.

Does the exe just do what it says Inserts rows into some table?

Yes.

One other way you could do it, is create a stored procedure that does the same thing that exe does, unless the exe does a whole more stuff.
create a sql job that calls that stored procedure and does the insert based on your logic

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update --,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'LiveDB')
AND OBJECT_ID=OBJECT_ID('ORDR') and CONVERT(VARCHAR, last_user_update, 120) >= dateadd(S,-2,getdate())

Maybe something like below. Although you may want to reconsider every 2 seconds, that seems rather extreme.

IF EXISTS(
    SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update --,*
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID( 'LiveDB')
    AND object_id = OBJECT_ID('ORDR') 
    AND last_user_update >= DATEADD(SECOND,-2,GETDATE())
)
BEGIN
    EXEC sys.xp_cmdshell '"\\SQL\app\InsertRows.exe"'
END /*IF*/

How to set the job schedule to 2 second? The maximum number accepts is 10 second.

another option is the following, capturing insert, update deletes using the OUTPUT . depends how are implementing your Insert update deletes. just another option.

even if the job cannot run every 2 seconds, another option is to keep track of things in a log tracking table that shows you when the last INSERTROWS happened

DECLARE @MyTableVar table( NewScrapReasonID smallint,  
                           Name varchar(50),  
                           ModifiedDate datetime);  
INSERT Production.ScrapReason  
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate  
        INTO @MyTableVar  
VALUES (N'Operator error', GETDATE()); 

The only way I can think off the top of my head is to schedule the main job for every 10 seconds. The first thing that job did is start 4 other jobs: the first thing the first job does is wait 2 seconds -- WAITFOR DELAY '00:00:02.000' -- then run; the second jobs waits 4 seconds, then runs; the third job waits 6 secs, etc..

But, again, I'd recommend backing off 2 seconds and going to 10 seconds. If you really need to see activity to the second or two, use some type of cdc or trace.

Thanks for your help.

Thanks yosiaz.