一小时后插入表中的更新记录 [英] Update record inserted in table after one hour

问题描述

我有一个在其中插入记录的交易表.表格结构如下.

  CreateDate | TransactionNo |状态 

现在，我想在每个交易记录创建一个小时后更改它们的状态.

例如如果某个记录是在上午10点插入的，并且状态未付款，那么我想触发一个事件或触发器，以便在上午11点将记录的状态更改为PAID.

我该怎么做??

解决方案

也许有另一种方法可以实现这一目标，但是我的方法如下:-

1)插入后在表上创建

一分钟后再次选择.

 从TblTransaction选择* 

结果:

快速注释:

1)键入 HOUR 而不是 MINUTE ，以便在一小时而不是一分钟后执行作业.

2)创建SQL代理作业的功能在SQL Server Express Edition中不可用，它是 sp_add_schedule(Transact-SQL):

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 ??

解决方案

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):

这篇关于一小时后插入表中的更新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！