locked
How do I create a count down timer in sql database RRS feed

  • Question

  • User-218090889 posted

    Hi friends,

    I am working on a web application that has several table in database,

    one of the tables ABC (not real name) has a column XYZ (not real name) which I will like to activate a count down (24hrs count down) when it is updated, if the count down elapse and no action is taken on the column it will perform a task and if action is taken before the  count down elapse it terminate the count down and perform a different task.

    Is any one with idea on how I could go about this?

    Note: I use C# Asp.Net.

    Tuesday, August 30, 2016 3:25 PM

Answers

  • User-286291038 posted

    Hi,

    Why not try using a SQL Job which does this? In your table, whenever an update occurs, you can maybe also update another table with a value that the job can check to see if some update had happened. If an update had happened, then your job, when it starts, it can reset the value in this table and do whatever it needs to do. If no update has occured, it can do the other task that it needs to do. You can configure this job to run every 24 hours.

    Reference,

    https://msdn.microsoft.com/en-us/library/ms190268.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 30, 2016 7:04 PM
  • User-595703101 posted

    Hi Enzyme,

    Another method can be using  SQL After Update Trigger as shown in below sample code

    CREATE TRIGGER dbo.Update_MyTable_checkColumn
     ON dbo.MyTable
    AFTER UPDATE
    AS
    
    declare @dt datetime
    set @dt = (select top 1 activationtime from CounterActivationTime where active = 1)
    if GETDATE() between @dt and DATEADD(hh,24,@dt)
    begin -- time check
    	select 
    		i.productname primaryKeyValue, 
    		i.checkColumn newValue, 
    		d.checkColumn oldValue
    	into #temptbl
    	from inserted i
    	inner join deleted d on i.productName = d.productName
    	where i.checkColumn <> d.checkColumn
    
    	if exists(select * from #temptbl)
    	begin
    		select 1
    	end
    	else
    	begin
    		select 0
    	end
    end -- time check
    
    GO 

    For above code to execute propertly, you will need a helper table

    create table CounterActivationTime(
    	id int identity(1,1), 
    	active tinyint,
    	activationtime datetime
    )
    insert into CounterActivationTime select 1, GETDATE()
    

    Inserting an active record will enable trigger to continue with data update check

    On the other hand, using triggers is not always a good way for performance

    And triggers always work set based, if more than one row affected on table data you need to perform for all rows. Escape using variables in triggers for storing updated data details.

    Hope it helps,

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 31, 2016 11:04 AM

All replies

  • User-286291038 posted

    Hi,

    Why not try using a SQL Job which does this? In your table, whenever an update occurs, you can maybe also update another table with a value that the job can check to see if some update had happened. If an update had happened, then your job, when it starts, it can reset the value in this table and do whatever it needs to do. If no update has occured, it can do the other task that it needs to do. You can configure this job to run every 24 hours.

    Reference,

    https://msdn.microsoft.com/en-us/library/ms190268.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 30, 2016 7:04 PM
  • User-595703101 posted

    Hi Enzyme,

    Another method can be using  SQL After Update Trigger as shown in below sample code

    CREATE TRIGGER dbo.Update_MyTable_checkColumn
     ON dbo.MyTable
    AFTER UPDATE
    AS
    
    declare @dt datetime
    set @dt = (select top 1 activationtime from CounterActivationTime where active = 1)
    if GETDATE() between @dt and DATEADD(hh,24,@dt)
    begin -- time check
    	select 
    		i.productname primaryKeyValue, 
    		i.checkColumn newValue, 
    		d.checkColumn oldValue
    	into #temptbl
    	from inserted i
    	inner join deleted d on i.productName = d.productName
    	where i.checkColumn <> d.checkColumn
    
    	if exists(select * from #temptbl)
    	begin
    		select 1
    	end
    	else
    	begin
    		select 0
    	end
    end -- time check
    
    GO 

    For above code to execute propertly, you will need a helper table

    create table CounterActivationTime(
    	id int identity(1,1), 
    	active tinyint,
    	activationtime datetime
    )
    insert into CounterActivationTime select 1, GETDATE()
    

    Inserting an active record will enable trigger to continue with data update check

    On the other hand, using triggers is not always a good way for performance

    And triggers always work set based, if more than one row affected on table data you need to perform for all rows. Escape using variables in triggers for storing updated data details.

    Hope it helps,

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 31, 2016 11:04 AM