Update record inserted in table after one hour

Question

I have a transaction table in which records are inserted. the table structure is below.

CreateDate|TransactionNo|Status

Now, I want to change the status of every transaction record after one hour of their creation.

For Ex. if a record is inserted at 10 AM with status unpaid, then I want to fire an event or trigger which change the status of record to PAID at 11 AM sharp.

How can I do this ??

Answer 1

Don't change the status! Instead, add a computed column with the status you really want:

alter table add real_status as (case when CreateDate >= dateadd(hour, 1, getdate()) then 'Paid' else status end);

(Actually, I would name the column in the table something like _status and call this status. You then have to be careful on updates and inserts.)

You can also do this using a view.

Why use this approach?

• Repeating transactions is expensive.
• The value changes at exactly the point when you want it to change. There are no delays waiting for jobs to be schedules or due to locks on tables, pages, rows, indexes, and so on.
• If the database goes down, it still works when the database comes back up.

Answer 2

I think that using Sql Server Agent for scheduled action is what you need. Check here:

How to run a stored procedure in sql server every hour?

Answer 3

Another possible solution would be to create a queue table of status updates. You could insert the transaction identifier, the time to change the status, and the target status into the queue table. Then check the queue table periodically for records where the change is due, make the update at that point, then delete the record from the queue. This could be triggered either through SQL Server Agent, or a separate service. If you use this approach, be sure to transact both ends (inserting the transaction and inserting the queue record, then later updating the transaction and deleting the queue record).

Here are some advantages / disadvantages that I can think of for using this method:

Advantages

• Decouples the update from the original transaction creation
• Automatically recovers from service failures
• Allows indexes to include the status field (unfortunately, using calculated fields may prevent this)

Disadvantages

• Updates will occur based on the polling interval, so will not be instantaneous. However, you can poll as frequently as needed (within reason).

Answer 4

Maybe there is an another approach for achieving this, but mine is as following:-

1) Create Trigger on Table After Insert.

This Trigger is responsible for executing a job.

2) Create a SQL Server Agent job Via T-SQL.

This a Job is responsible for Updating the table after one hour.

The Complete Demo: {Tested also}

This Demo is Updating The Table after one Minute.

Create database DBTest
go

use DBTest
go


Create table TblTransaction (CreateDate datetime,
                            TransactionNo int, 
                            Status varchar (10))
go

USE msdb
go
Create procedure [dbo].[sp_add_job_quick] 
@job nvarchar(128),
@mycommand nvarchar(max), 
@servername nvarchar(28),
@startdate nvarchar(8),
@starttime nvarchar(8)
as
--Add a job
EXEC dbo.sp_add_job
    @job_name = @job ;
--Add a job step named process step. This step runs the stored procedure
EXEC sp_add_jobstep
    @job_name = @job,
    @step_name = N'Update Status After one Hour',
    @subsystem = N'TSQL',
    @command = @mycommand
--Schedule the job at a specified date and time
exec sp_add_jobschedule @job_name = @job,
@name = 'MySchedule',
@freq_type=1,
@active_start_date = @startdate,
@active_start_time = @starttime
-- Add the job to the SQL Server Server
EXEC dbo.sp_add_jobserver
    @job_name =  @job,
    @server_name = @servername

go

use DBTest
go

Create Trigger trg_UpdateRecordAfterOneHour on TblTransaction
After insert
as
declare @JobName varchar (100),
        @TransactionNo int,
        @mycommand  varchar (512),
        @startdate varchar (100),
        @startTime varchar(100)

select @TransactionNo = TransactionNo  from inserted
set @JobName = 'MyJob_' + convert(nvarchar(MAX), GETDATE(), 113)
set @mycommand = 'Update DBTest..TblTransaction set status = ''PAID'' where TransactionNo =' + convert(varchar(100), @TransactionNo)
set @startdate =  convert(nvarchar(MAX), GETDATE(), 112)

set @starttime = Replace(convert(varchar(10), dateadd(MINUTE,1,GETDATE()), 108),':','')

exec msdb.dbo.sp_add_job_quick 
                    @JobName , 
                    @mycommand,
                    @@servername,
                    @startdate,
                    @starttime

Let's Insert an Record , Then Select this Record.

insert TblTransaction values (getdate(),1,'UnPaid')

 select * from TblTransaction

Result:

Select Again after one minute.

 select * from TblTransaction

Result:

Quick Notes:

1) Type HOUR instead of MINUTE for executing the job after one hour rather than one minute.

2) The functionality of creating SQL Agent Jobs is not available in SQL Server Express Edition, it is available for Enterprise , Business Intelligence, Standard & Web editions.

3) By default, the SQL Server Agent service is disabled when SQL Server is installed unless the user explicitly chooses to autostart the service.

Update according to a below comment:-

4) The Job that Created is working for a once, according the demo while creating [dbo].[sp_add_job_quick] , I passed 1 for @freq_type as following:

@freq_type=1

The accepted values for @freq_type is as following:

╔═══════╦════════════════════════════════════════╗
║ Value ║              Description               ║
╠═══════╬════════════════════════════════════════╣
║     1 ║ Once                                   ║
║     4 ║ Daily                                  ║
║     8 ║ Weekly                                 ║
║    16 ║ Monthly                                ║
║    32 ║ Monthly, relative to freq_interval     ║
║    64 ║ Run when SQLServerAgent service starts ║
║   128 ║ Run when the computer is idle          ║
╚═══════╩════════════════════════════════════════╝

for more details sp_add_schedule (Transact-SQL):