triggering a view filtered by getdate()

Question

triggering a view filtered by getdate()

Udi Mishan

Grasshopper

Points: 16
More actions
January 22, 2007 at 5:49 am

#194006

lets say i have a view of the follwing:
CREATE
VIEW [dbo].[vw_openEvents]
AS
SELECT EvId
FROM Events
WHERE EvDateTime>GETDATE()
1. Every time Accessing the view will get differrent results even if no data changes occored in the DB ... (inserting ,deleting updating), since some events may expire with every call.
2. This view will show future events as you all can see.
3. My Question Is:
Can i make some kind of a trigger on records that left the view?
In other words, lets say i want to update AUTOMATICLY IN REAL TIME every record of tblEvents when its
EvDateTime is now less then GETDATE()?
Is the only way to do it is to run a job with an interval of 1 minute checking for expired events?
Or is there some trick enabling me to trigger this automaticly?
thank you very much...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You can add a calculated field in the view such as ExpireTime which would contain the number of minutes / seconds or whatever that the event will become invald. Then once you present the data, read the rows and fetch the minimum value foir the expiretime and then refresh the data once that limit is reached.

Udi Mishan Grasshopper Points: 16 More actions · Answer 2

hmm... nice idea...!

But, lets say i need to do the next update in 2 hours,

how can i exec a certain sp in a specific time, and then again fetch the next closest time,

lets say 1 hour later, and exec the update sp again exactly 1 hour later, and so on...?

thank you.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I'm not sure I'm following you. I'm gonna tell you to check out the datediff function in the books online. That should help you with what you want.

Lowell SSC Guru Points: 323518 More actions · Answer 4

don't try and find the items that are no longer in the view....create the OPPOSITE of the view, and have a schedule job email you or something for every item in the list: If the email is sent, update a notification column.

CREATE VIEW [dbo].[vw_ExpiredEvents]

AS

SELECT EvId

FROM Events

WHERE EvDateTime < GETDATE()

AND EmailSent is not null

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rob Farley SSCrazy Points: 2739 More actions · Answer 5

Or store the last time you checked somewhere. Perhaps make it a table-returning function instead, so that you can throw in a little more functionality. After all, a view isn't really meant to be an ever-changing thing really... I mean, it could... but it would be like having a select-trigger on a table, which you don't tend to do.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au