Solved

Help with a trigger or computed column

Posted on 2013-10-22
9
206 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 250 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 125 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 125 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 250 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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

688 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