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
thanks
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
thanks
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.
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:
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:
CREATE PROCEDURE BootstrapBackgroundTask
AS
WAITFOR DELAY '00:00:30'
EXEC YourDB..DoBackgroundTask
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.
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.
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.
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.
© 2022 - 2024 — McMap. All rights reserved.