Home / Questions / 375569
Accepted
Franco Torres
Asked: 2020-07-23 15:32:06 +0800 CST 2020-07-23 15:32:06 +0800 CST

How to update a value in sql server automatically when a date arrives

  • 772

I have a table called Cuota, which has a Fecha_vencimiento(of type Date) and a estado(of type varchar). I need that when the expiration date arrives, the status changes automatically from 'VALIDA'to 'VENCIDA'.

I don't know if I have to do it with a stored procedure or with a trigger; if it is with any of these 2, how do I make it run automatically when the expiration date arrives?

sql-server

2 Answers

  1. You can achieve this with the SQL Server Agent, however you must validate that you have access to it or that your version of SQL Server has or supports it. You can see some examples on the Microsoft page .

    • 1
  2. Best Answer

    Given the description of the problem you are giving, I am going to answer what you are NOT asking, since this is the typical case of the xy problem .

    This is because although SQL Server has the mechanisms to perform the task you are looking for, there really is no need to complicate things so much if you follow the basic principle of not storing derived information in tables.

    That is, you already have the expiration date and you can find out the current date at any time, with the function getdate(). A simple comparison will tell you if a quota is expired or not.

    Keeping the same information stored in another column, but in a different way, for example, knowing if it is expired or not, is redundant and is usually a source of problems due to programming and/or configuration errors.

    That said, with SQL server you also don't need to write the rule that tells you the status of the record in each query, since you can pull the computed columns .

    For example, look at the create statement for this table:

    create table DatosExpiran (
    id                 bigint not null identity primary key
  , FechaExpiracion    datetime not null
  , compEstaExpirado   as case when FechaExpiracion < getdate() then 0 else 1 end
  , compEstadoActual   as case when FechaExpiracion < getdate() then 'VENCIDO' else 'VIGENTE' end
);

    If we insert these values:

    insert into DatosExpiran (FechaExpiracion) 
values ('20200101 17:12:14')
     , ('20211231 11:14:12')
     , (dateadd(ss, 60, getdate()));

    When executing this query:

    select *, getdate() FechaActual
  from DatosExpiran;

    I get, at startup, these values:

    id                   FechaExpiracion         compEstaExpirado compEstadoActual FechaActual
-------------------- ----------------------- ---------------- ---------------- -----------------------
1                    2020-01-01 17:12:14.000 0                VENCIDO          2020-07-23 22:13:39.583
2                    2021-12-31 11:14:12.000 1                VIGENTE          2020-07-23 22:13:39.583
3                    2020-07-23 22:14:14.600 1                VIGENTE          2020-07-23 22:13:39.583

(3 rows affected)

    But just over 1 minute later, I get this:

    id                   FechaExpiracion         compEstaExpirado compEstadoActual FechaActual
-------------------- ----------------------- ---------------- ---------------- -----------------------
1                    2020-01-01 17:12:14.000 0                VENCIDO          2020-07-23 22:15:19.107
2                    2021-12-31 11:14:12.000 1                VIGENTE          2020-07-23 22:15:19.107
3                    2020-07-23 22:14:14.600 0                VENCIDO          2020-07-23 22:15:19.107

(3 rows affected)

    As you can see, the last row has expired automatically when the expiration date/time arrives without the need to do anything more than the design of the table with calculated columns.

    • 1