Help with a trigger or computed column

I had already asked this, and then closed the case.  However, I realize the answer I accepted did not work.  So.....

I have a table with an end date column and a  long value that is a status.  I want to set the status to a specific value whenever the current date is great than the end date.  

A computed column will not work, unless I do not understand something.  A computed column is just that - always computed.   What I need to do is automatically update a non-computed status column based on the fact that the current date is greater than the EndDate column.   My non-computed status column is also updated via code based on other factors.
LVL 1
HLRosenbergerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
COANetworkConnect With a Mentor Commented:
very easy.  use the code suggested by  auke_t  as the basis for your update.  In SSMS, expand SQL Server Agent node, right-click Jobs node then select "New Job".  follow wizard.  very self-explanatory
0
 
COANetworkConnect With a Mentor Commented:
You will need to somehow trigger the update.  Either use a TRIGGER on insert or update of the end date column to update your status, or create a job to run on schedule, and update all status columns to your specific value where end date is > current date.  I can be more specific if you tell me when you want status updated - on current date rollover, or on update of the end date column.
0
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
CREATE TRIGGER MyTable_Update ON MyTable
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(EndDate)
BEGIN
   UPDATE MyTable
   SET status = somevalue
   FROM MyTable
   INNER JOIN INSERTED AS i
      ON MyTable.ID = i.ID
   INNER JOIN DELETED AS d
      ON i.ID = d.ID
   WHERE i.EndDate > GETDATE()
END
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
auke_tConnect With a Mentor Commented:
IMHO you should do this with a scheduled stored procedure not a trigger as a trigger will only fire after an update or an insert.

create procedure p_UpdateStatus
as
update
  yourtable
set
  status = somevalue
where
  EndDate > GETDATE()
and
  status <> somevalue
0
 
HLRosenbergerAuthor Commented:
What about this - I have a computed column.  All computed column are updated when they are accessed, correct?  So this column would be a boolean that reflects when the current date is greater then my End Date.   Then, I would have a trigger that looks at the computed column and set the Status column accordingly.   Would this work?
0
 
auke_tCommented:
All computed columns are computed when they are accessed, not updated
0
 
COANetworkCommented:
Pointless, because a trigger does not "look".  The trigger only fires if there is an insert or an update.  it is not something that's continuously monitoring your records.  nor is it aware of the passage of time.  That is why I initially asked if you want your status updated on schedule, or at the time of insert/update - in the former case you would use a scheduled SQL Agent job, in the latter - a trigger.
0
 
HLRosenbergerAuthor Commented:
On insert or update will not work I guess because no insert or update is being done.  The record already exists and I need to update the status column based on a comparing the GetDate() with the EndDate column.

I have never used an SQL Agent job.  Easy to setup?
0
 
HLRosenbergerAuthor Commented:
thanks to all...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.