sql expiry date use Trigger OR Schedule a Job I have this table :



Now, i like the way when the date expires. EndTime &lt; GETDATE() 

expire field is true.

What method should I use? use a trigger to true or a schedule job that run before run any  query  in this table?
something like below should get you started ... 

CREATE TABLE [dbo].[patientInfo]
(
id int,
patientid int,
  starttime datetime,
  endtime datetime,
  doctorid int
)
GO

ALTER TABLE patientInfo ADD 
    expire AS CASE WHEN endtime &lt; GETDATE()THEN 1 ELSE 0 END
GO

    declare @now datetime=getdate()

INSERT into patientInfo (id, patientid, starttime, endtime, doctorid)
SELECT 1,1,@now,@now-1,1
UNION ALL SELECT 2,2,@now,@now+1,2
UNION ALL SELECT 3,3,@now, @now+2,3
UNION ALL SELECT 4,4,@now, @now-2,4


SQLFiddle here