How to mark an item as “Expired” when it's ExpiryDate value is in the past?

Question

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.

Answer 1

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

Answer 2

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.