Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table of items, each of which has a Status and ExpiryDate.

What is the best way of enforcing this:

when ExpireyDate > DateTime.Now
update Status to "expired"

I could have a stored procedure which is run periodically, which checks all items and updates as needed. However I would like to avoid any delay of the item expiring, and being marked as expired.

Is there a better way of doing this? Some kind of constraint in the Db?

How would an auction site do this, for example? Where the 'closing' of an auction at a very precise time is important.

Edit

I marked gbn s answer as the solution, as it provides a solution to my issue. However I think user212102 is getting at the crux of the issue - why am I storing status, rather than computing it based on the state of the item? I am going to look into a better way of achieving this.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Assuming you want the expired flag to be real time...

You can use a view to access the data that has a CASE with GETDATE(). A computed column also works.

Note: you can't index the view because GETDATE is non-deterministic

CREATE TABLE myTestTable (foo int IDENTITY (1,1), bar varchar(100), ExpiryDate datetime)
GO
DECLARE @now datetime= GETDATE()
INSERT myTestTable (bar, ExpiryDate)
SELECT 'row1', @now - 2.1
UNION ALL SELECT 'row2', @now - 1.6
UNION ALL SELECT 'row3', @now - 1
UNION ALL SELECT 'row4', @now - 0.1
UNION ALL SELECT 'row5', @now + 2.1
UNION ALL SELECT 'row6', @now + 1.6
UNION ALL SELECT 'row7', @now + 1
UNION ALL SELECT 'row8', @now + 0.1
GO

CREATE VIEW MytestTableWithEXPIRED
WITH SCHEMABINDING
AS
SELECT
   foo, bar, ExpiryDate,
   CASE WHEN ExpiryDate < GETDATE()THEN 1 ELSE 0 END AS HasExpiredView
FROM
   dbo.myTestTable
GO
SELECT * FROM MytestTableWithEXPIRED
GO

ALTER TABLE MytestTable ADD 
    HasExpiredCol AS CASE WHEN ExpiryDate < GETDATE()THEN 1 ELSE 0 END
GO
SELECT * FROM MytestTable
GO
share|improve this answer
1  
or CASE WHEN ExpiryDate < GETDATE() THEN 'expired' ELSE status END AS real_status if there are a number of possible statuses that are all overridden by 'expires' –  Jack Douglas May 29 '11 at 13:52

Sounds like Status is a derived value anyway? So either it is not needed or use a computed column if it is fully dependent on the value(s) in other column(s) in the same row.

share|improve this answer
    
I think this is the real issue - I should be calculating the Status based on other factors, rather than storing and maintaining a separate status field. –  elwyn May 31 '11 at 20:59
1  
The DBMS would automatically maintain the computed column for you; that's what it is designed to do. MSDN Computed Columns And very easy to set-up. A bit like a computed cell in a spreadsheet. –  user212102 Jun 4 '11 at 11:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.