Jobs in SQL Server proxy

My project is similar to that of Baidu. Of course it is much easier to know than Baidu. On a problem sharing platform, if the user does not handle the problem within the specified period (the user does not take the correct answer, closes the problem, or simply forgets this code ), the expiration date is regarded as an expiration date, and the user will be fined points. Because the expiration time of each problem is different, it is impossible for people to manually deduct points when the problem expires.

One solution is to trigger the query method on the page page_load after the user logs on to query whether the current user has expired. If yes, the score is deducted. However, if the user does not log on, the score will not be deducted. In fact, the problem has expired and does not meet the requirements. Then, you only need to check whether all problems expire when each user logs on. In this case, if the website traffic is too large, the performance will be difficult to guarantee.

Solution 2. SQL server2005 has a function called SQL Server proxy job. One function is to trigger an SQL statement by setting a time point. This is just in line with my requirements.

Right-click "SQL Server proxy" and select "new job ". The dialog box is displayed. The "Select page" option on the left has six settings. Generally, you only need to set the first three items. Set some simple names and descriptions in "regular. OK. You can add execution steps and SQL statements in "Steps. Select the database and click OK. Go to "plan" Settings-New. Here you can set the execution frequency and execution time of your job.

Paste several images at a glance.

 

Plan:

Paste the SQL code:

 

Code
-- Update the [work_status_id] field of the problem table and the transaction that deducts user points that are not processed due to expiration issues.
Begin transaction
Declare @ errornum int
-- Points deducted
Update [dbknow]. [DBO]. [tbzduser_base]
Set [useroffer] = [useroffer]-20
Where userid
In
(Select userid from [tbzdquestion] Where
Datediff ("Hour", [release_time], getdate () & gt; 336
And work_status_id <5)
Set @ errornum = @ errornum + @ Error
-- Update [work_status_id] and [operation_time]
Update [dbknow]. [DBO]. [tbzdquestion]
Set [operation_time] = getdate ()
, [Work_status_id] = 5
Where
Datediff ("Hour", [release_time], getdate () & gt; 336
And [work_status_id] <5
Set @ errornum = @ errornum + @ Error
-- Determine whether an error exists
If @ errornum <> 0
Begin
Print 'error occurred'
Rollback transaction
End

Else
Begin
Print 'Operation successful'
End
Commit transaction