[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Help with a trigger or computed column

Posted on 2013-10-22
9
Medium Priority
?
212 Views
Last Modified: 2013-10-23
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.
0
Comment
Question by:HLRosenberger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 9

Assisted Solution

by:COANetwork
COANetwork earned 1000 total points
ID: 39592456
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
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 500 total points
ID: 39592666
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
 
LVL 9

Assisted Solution

by:auke_t
auke_t earned 500 total points
ID: 39593699
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:HLRosenberger
ID: 39594164
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
 
LVL 9

Expert Comment

by:auke_t
ID: 39594176
All computed columns are computed when they are accessed, not updated
0
 
LVL 9

Expert Comment

by:COANetwork
ID: 39594178
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
 
LVL 1

Author Comment

by:HLRosenberger
ID: 39594210
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
 
LVL 9

Accepted Solution

by:
COANetwork earned 1000 total points
ID: 39594241
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
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 39594275
thanks to all...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question