CODEWITHSUNDEEP
sqlsql-server
Rita Shroff
Rita Shroff

Reputation: 175

How to change a field data after time t in a table?

Is it possible to change the data in table automatically after time i.
Say time i = 10 sec. Table t, has column x1,x2,x3.
x3 is boolean as datatype. x3'll be false by

Now suppose I changed the value in t.x3 (any row) to True then after 10 sec it should change itself to False.

Is it possible.? By some trigger or cursor or procedure or something?
Alteast need a keyword to google it. Thank You!

Upvotes: 3

Views: 914

Answers (3)

dani herrera
dani herrera

Reputation: 51645

This scenario you are asking for looks like an anti-pattern. Exists a command to wait for random amount of time ( WAITFOR DELAY '00:00:02'; ) but, usually, is not a good idea to use it:

Caution+

Including WAITFOR will slow the completion of the SQL Server process and can result in a timeout message in the application. If necessary, adjust the timeout setting for the connection at the application level.

In my opinion, the easy way is to create a view over your table and calculate the field on the fly:

CREATE TABLE t ( PrimaryKey ....,
                 x3 bit, 
                 t datetime default gettime(), 
               );

CREATE VIEW v as
SELECT PrimaryKey,
       (CASE WHEN x3 = 1 and datediff(second,  t, getdate()) > 10 
             THEN 1 
             ELSE 0 
        END ) as x3  --<-- lets supose this is your field.
FROM t;

Then, you can select from view to check for your calculate field:

SELECT x3 FROM v; --<-- select from v instead from t.

You can use a trigger to keep t field up to date on change x3:

CREATE TRIGGER updateModified
ON dbo.t
AFTER UPDATE 
AS
   UPDATE dbo.t
   SET t = getdate()
   FROM Inserted i
   WHERE dbo.t.PrimaryKey = i.PrimaryKey

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can do this without a trigger. Just use a computed column.

For instance:

create table t (dte datetime,
                flag as (case when dte < dateadd(second, -10, getdate()) then 0 else 1
                         end)
               );

This is a computed column. It will recalculate when it is queried, doing the calculation you are asking for.

Upvotes: 2

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

You can use a trigger to automatically update the values after something has changed on the table. But this change will be affected within milliseconds as the trigger executes straight after the update/insert/delete.

The second method you can do is to create a scheduled Database job which will run every 10 seconds (In this case) or any specific interval, which will make the updates in the required columns.

Refer this Article for more information on scheduling the DB Jobs

Upvotes: 0

Related Questions