Executing a stored procedure from a trigger after a delay

You have a SQL agent job that runs regularly and pulls the stored procedure parameters from the table - the rows should also indicate when their stored procedure should run, so the SQL agent job will only select rows that should / are a bit expired, It should delete the rows or mark them after calling the stored procedure.

Then in the trigger, just insert a new row into the same table.

- , - - - .

,

CREATE PROCEDURE DoMagic
    @Name varchar(20),
    @Thing int
AS
  ...

:

CREATE TABLE MagicDue (
    MagicID int IDENTITY(1,1) not null, --May not be needed if other columns uniquely identify
    Name varchar(20) not null,
    Thing int not null,
    DoMagicAt datetime not null
)

SQL:

WHILE EXISTS(SELECT * from MagicDue where DoMagicAt < CURRENT_TIMESTAMP)
BEGIN
    DECLARE @Name varchar(20)
    DECLARE @Thing int
    DECLARE @MagicID int

    SELECT TOP 1 @Name = Name,@Thing = Thing,@MagicID = MagicID from MagicDue where DoMagicAt < CURRENT_TIMESTAMP

    EXEC DoMagic @Name,@Thing

    DELETE FROM MagicDue where MagicID = @MagicID
END

:

CREATE TRIGGER Xyz ON TabY after insert
AS
    /*Do stuff, maybe calculate some values, or just a direct insert?*/
    insert into MagicDue (Name,Thing,DoMagicAt)
    select YName,YThing+1,DATEADD(minute,30,CURRENT_TIMESTAMP) from inserted

, , . , , - , " ", :

CREATE PROCEDURE DoBackgroundTask
AS

     WHILE 1=1
     BEGIN
         /* Add whatever SQL you would have put in an agent job here */

         WAITFOR DELAY '00:05:00'
     END

, master, 30 , :

CREATE PROCEDURE BootstrapBackgroundTask
AS
    WAITFOR DELAY '00:00:30'
    EXEC YourDB..DoBackgroundTask

, sp_procoption:

EXEC sp_procoption N'BootstrapBackgroundTask', 'startup', 'on'

- .