延迟一段时间后从触发器执行存储过程

I want to call stored procedure from a trigger, how to execute that stored procedure after x minutes? I'm looking for something other than WAITFOR DELAY

我想从触发器调用存储过程,如何在x分钟后执行该存储过程?我正在寻找除了WAITFOR DELAY之外的其他东西

thanks

2 个解决方案

#1

Have an SQL Agent job that runs regularly and pulls stored procedure parameters from a table - the rows should indicate also when their run of the stored procedure should occur, so the SQL Agent job will only pick rows that are due/slightly overdue. It should delete the rows or mark them after calling the stored procedure.

拥有一个定期运行的SQL代理作业并从表中提取存储过程参数 - 这些行还应指示何时应该运行存储过程,因此SQL代理作业将仅选择到期/略微过期的行。它应该在调用存储过程后删除行或标记它们。

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

然后,在触发器中,只需在同一个表中插入一个新行。

You do not want to be putting anything in a trigger that will affect the execution of the original transaction in any way - you definitely don't want to be causing any delays, or interacting with anything outside of the same database.

您不希望将任何内容放入触发器中以任何方式影响原始事务的执行 - 您绝对不希望导致任何延迟,或者与同一数据库之外的任何内容进行交互。

E.g., if the stored procedure is

例如,如果存储过程是

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

Then we'd create a table:

然后我们创建一个表:

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
)

And the SQL Agent job would do:

并且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

And the trigger would just have:

触发器只有:

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

If you're running in an edition that doesn't support agent, then you may have to fake it. What I've done in the past is to create a stored procedure that contains the "poor mans agent jobs", something like:

如果您运行的版本不支持代理,那么您可能需要伪造它。我过去所做的是创建一个包含“穷人代理工作”的存储过程,如:

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

Then, create a second stored procedure, this time in the master database, which waits 30 seconds and then calls the first procedure:

然后,创建第二个存储过程,这次是在master数据库中,等待30秒,然后调用第一个过程:

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

And then, mark this procedure as a startup procedure, using sp_procoption:

然后,使用sp_procoption将此过程标记为启动过程:

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

And restart the service - you'll now have a continuously running query.

并重新启动服务 - 您现在将有一个持续运行的查询。

#2

I had kind of a similar situation where before I processed the records inserted into the table with the trigger, I wanted to make sure all the relevant related data in relational tables was also there.

我有类似的情况,在我用触发器处理插入表中的记录之前,我想确保关系表中的所有相关相关数据也在那里。

My solution was to create a scratch table which was populated by the insert trigger on the first table.

我的解决方案是创建一个临时表,该表由第一个表上的insert trigger填充。

The scratch table had a updated flag, (default set to 0), and an insert get date() date field, and the relevant identifier from the main table.

临时表具有更新的标志(默认设置为0),以及insert get date()日期字段,以及主表中的相关标识符。

I then created a scheduled process to loop over the scratch table and perform whatever process I wanted to perform against each record individually, and updating the 'updated flag' as each record was processed.

然后,我创建了一个调度进程来遍历临时表,并分别对每个记录执行我想要执行的任何进程,并在处理每个记录时更新“更新标志”。

BUT, here is where I was a wee bit clever, in the loop over process looking for records in the scratch table that had a update flag = 0, I also added the AND clause of AND datediff(mi, Updated_Date, getdate())> 5. So the record would not actually be processed until 5 minutes AFTER it was inserted into the scratch table.

但是,这里是我有点聪明的地方,在循环过程中寻找刮刮表中有更新标志= 0的记录,我还添加了AND datediff的AND子句(mi,Updated_Date,getdate()) > 5.因此,记录在插入临时表后5分钟才会实际处理。

#1