July 1, 2003 at 3:49 am
Whats the best way to scedule a series of queries after a table has been updated? Thanks guys
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
July 1, 2003 at 4:03 am
Scheduled stuff is usually done by setting up a job in SQL agent. You could set to run at a certain time when you know the step before would be done, or you could run more frequently to check to see if the step before was complete.
Andy
July 1, 2003 at 4:45 am
I was thinking more along the lines of using triggers to fire off the queries after an INSERT. Would this work?
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
July 2, 2003 at 2:40 am
actually..i think i can use scheduled jobs toaction an update and then schedule sproc's to implement a string of queries......
[/quote]
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
July 2, 2003 at 2:51 am
You could use triggers without any problem. But you have to take care that they are fired upon EVERY insert or update you do.
So if the load of the queries you need to fire is high, and there are a lot of one record inserts, you might run into performance problems.
In that case, it might be better to use a 'batching' technique like a scheduled job.
July 2, 2003 at 3:32 am
I think your right....scheduling is the way I think along with some nice helpful stored procs.....
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
July 2, 2003 at 4:39 pm
You can do some cool things directly in tSQL using "WAITFOR" with "DELAY" or "TIME".
Example below...
use Northwind--for this example
IF Object_ID('tempdb..##NewCategories') is not null drop table ##NewCategories
Create Table ##NewCategories (CategoryID int, CreatedDate datetime)--This is the table being updated by the loop below
go
declare @iIDmin int,
@iIDmax int--variable to hold max ID in Table
Select @iIDmin = (Select top 1 CategoryID from Categories (nolock) order by CategoryID desc)
/*---------------------------------
Now we're ready to go!
*/----------------------------------
While 1=1--Forever....
begin
WAITFOR DELAY '00:00:30'--Wait one minute
IF @iIDmin <> (Select top 1 CategoryID from Categories (nolock) order by CategoryID desc)
Begin
Select @iIDmax = (Select top 1 CategoryID from Categories (nolock) order by CategoryID desc)
Insert ##NewCategories--Insert all new rows
Select CategoryID, getdate()
From Categories
Where CategoryID between @iIDMin + 1 and @iIDmax
select @iIDmin = @iIDmax--reset min value
END
END
Signature is NULL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply