Last post Aug 31, 2016 11:04 AM by eralper
Aug 30, 2016 03:25 PM|Enzyme|LINK
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.
Aug 30, 2016 07:04 PM|Nataraj Gandhi Arunachalam|LINK
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.
Aug 31, 2016 11:04 AM|eralper|LINK
Another method can be using
SQL After Update Trigger as shown in below sample code
CREATE TRIGGER dbo.Update_MyTable_checkColumn
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
from inserted i
inner join deleted d on i.productName = d.productName
where i.checkColumn <> d.checkColumn
if exists(select * from #temptbl)
end -- time check
For above code to execute propertly, you will need a helper table
create table CounterActivationTime(
id int identity(1,1),
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,