Solved

Help with a trigger or computed column

Posted on 2013-10-22
9
203 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Designer 19 39
MS SQL Merging data from table into another table 1 32
sql 2014,  lock limit 5 30
sql server tables from access 18 17
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 setup several different housekeeping processes for a SQL Server.

776 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