Solved

Help with a trigger or computed column

Posted on 2013-10-22
9
201 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
  • 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now