在时间延迟后从触发器执行存储过程 [英] Execute stored procedure from a Trigger after a time delay

问题描述

我想从触发器调用存储过程，如何在 x 分钟后执行该存储过程?我正在寻找 WAITFOR DELAY

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

谢谢

推荐答案

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

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.

然后，在触发器中，在同一个表中插入一个新行.

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.

例如，如果存储过程是

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
)

SQL 代理工作将执行以下操作:

And the SQL Agent job would do:

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

<小时>

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

---

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

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

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

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

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

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

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

然后重新启动服务 - 您现在将拥有一个持续运行的查询.

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

这篇关于在时间延迟后从触发器执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！