locked
Updating DB record Automatically from Expiry Date RRS feed

  • Question

  • User-1901014284 posted

    Hi,

    I have within my database a table which contains all contracts for clients which includes 2 columns in particular called Status and ExpiryDate.  What I am looking to do is automatically update the Status column of the table if the ExpiryDate has passed (or if today's date is the ExpiryDate). Would this be possible through a Trigger or Stored Procedure within MSSQL and if so how could implement this?

    Many thanks for any assistance, it is greatly appreciated.

    Jonny

    Wednesday, October 10, 2018 1:31 PM

All replies

  • User753101303 posted

    Hi,

    Or maybe a computed column depending on your database and what means this status or if you have other dates that could make it to be entirely computed ?

    See https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-2017 for SQL Server.

    Wednesday, October 10, 2018 2:00 PM
  • User475983607 posted

    I have within my database a table which contains all contracts for clients which includes 2 columns in particular called Status and ExpiryDate.  What I am looking to do is automatically update the Status column of the table if the ExpiryDate has passed (or if today's date is the ExpiryDate). Would this be possible through a Trigger or Stored Procedure within MSSQL and if so how could implement this?

    Many thanks for any assistance, it is greatly appreciated.

    The status field is redundant.  Just use the ExpiryDate to determine if the record is expired. 

    Wednesday, October 10, 2018 2:01 PM
  • User-1901014284 posted

    Hi,

    Thank you for your response, the reason I am looking to update the Status from the Expiry Date is in my project I have 3 Gridviews and these are all populated based on the status column within my table (Live, Ex-Client and On-Hold) the only way I am able to amend this at the moment is by manually updating the system whereas I would like the system to automatically update the Status Column on/after the ExpiryDate so when the program is run the Expired Contracts are displayed in the correct Gridview. I hope this helps expand on my query, I will take a look into computed column's.

    Many thanks

    Jonny

    Wednesday, October 10, 2018 2:11 PM
  • User753101303 posted

    So it seems it could be :

    ActualStatus AS CASE WHEN ExpiryDate>GETUTCDATE() THEN 'Ex-Client' ELSE ProposedStatus END

    ie a "manually" entered status is overriden if needed.

    Or if you have dates for each step you could compute entirely the status :

    Status AS CASE WHEN ExpiryDate>GETUTCDATE() THEN 'Ex-Client' WHEN AccountConfirmed IS NOT NULL THEN 'Live' ELSE 'On-Hold' END

    Wednesday, October 10, 2018 2:48 PM
  • User475983607 posted

    Thank you for your response, the reason I am looking to update the Status from the Expiry Date is in my project I have 3 Gridviews and these are all populated based on the status column within my table (Live, Ex-Client and On-Hold) the only way I am able to amend this at the moment is by manually updating the system whereas I would like the system to automatically update the Status Column on/after the ExpiryDate so when the program is run the Expired Contracts are displayed in the correct Gridview. I hope this helps expand on my query, I will take a look into computed column's.

    This is a design bug if status is solely determined by the ExpiryDate.  In which case you do not need the Status field.  If you've bound UI to this field simply use a query to get the result set as suggested by PatriceSc or create a database View.

    The alternative to run a job at some frequency.  This causes a margin of error related to the frequency.  It's much easier to just determine if the date passed on demand.

    Wednesday, October 10, 2018 3:32 PM