/ ** /
/ * - Work processing instance can automatically delete jobs according to SendTab's SendTime and when the job is completed - Zou Jian 2004.04 (please keep this information) - * /
-
Example
-
Test table
Create
TABLE
dbo.sendtab (ID
int
Identity
(
1
,
1
), Name
nvarchar
(
10
), Sendtime
Datetime
, AcceptUnit
VARCHAR
(
10
), SendUnit
VARCHAR
(
10
), Content
nvarchar
(
4000
))
Create
TABLE
dbo.accetab (ID
int
Identity
(
1
,
1
), Name
nvarchar
(
10
), SendUnit
VARCHAR
(
10
), Acceptunit
VARCHAR
(
10
), Content
nvarchar
(
4000
))
Go
-
Create a process of stored procedures
Create
PROC
DBO.P_JOBSET
@ID
int
,
-
SENDTAB ID to be processed
@is_delete
Bit
=
0
-
Whether it is deleted, whether it is 0, 1 is 1
AS
Declare
@dbname
Sysname,
@JobName
Sysname,
@Date
int
,
@Time
int
-
Job name and run time
SELECT
@JobName
=
N
'
Timed time
'
CAST
(
@ID
AS
nvarchar
),
@Date
=
Convert
(
VARCHAR
, Sendtime,
112
),
@Time
=
Replace
(
Convert
(
VARCHAR
, Sendtime,
108
),
'
:
'
,
'' '
)
From
dbo.sendtab
WHERE
id
=
@ID
-
If the job already exists, delete
IF
Exists
(
SELECT
1
From
MSDB.DBO.SYSJOBS
WHERE
Name
=
@JobName
)
EXEC
MSDB.DBO.SP_DELETE_JOB
@JOB_NAME
=
@JobName
IF
@is_delete
=
1
Return
-
Create job
EXEC
MSDB.DBO.SP_ADD_JOB
@JOB_NAME
=
@JobName
,
@Delete_level
=
1
-
Create job steps
Declare
@SQL
VARCHAR
(
800
)
SELECT
@SQL
=
N
'
- Job wants to implement insert dbo.accetab (name, sendunit, acceptunit, content) select name, acceptunit, sendunit, contentFrom dbo.sendtabwhere id =
'
CAST
(
@ID
AS
VARCHAR
),
@dbname
=
DB_NAME
()
EXEC
MSDB.DBO.SP_ADD_JOBSTEP@job_name
=
@JobName
,
@Step_name
=
N
'
Send processing steps
'
,
@subsystem
=
'
TSQL
'
,
@Database_name
=
@dbname
,
@Command
=
@SQL
,
@Retry_attempts
=
5
,
-
number of retries
@Retry_Interval
=
5
-
Retry interval
-
Create schedule
EXEC
MSDB.DBO.SP_ADD_JOBSCHEDULE
@JOB_NAME
=
@JobName
,
@name
=
N
'
Schedule
'
,
@tenabled
=
1
,
@freq_type
=
1
,
@Active_start_date
=
@Date
,
@Active_start_time
=
@Time
-
Add a target server
EXEC
MSDB.DBO.SP_ADD_JOBSERVER
@JOB_NAME
=
@JobName
,
@server_name
=
N
'
(Local)
'
Go
-
Create a process of trigger (new / modified)
Create
Trigger
TR_INSERT_UPDATE
On
dbo.sendtab
For
Insert
,
Update
AS
Declare
@ID
int
Declare
TB
Cursor
Local
For
SELECT
id
From
insert
Open
TB
Fetch
TB
INTO
@ID
While
@@ fetch_status
=
0
Begin
EXEC
DBO.P_JOBSET
@ID
=
@ID
Fetch
TB
INTO
@ID
End
Close
TB
Deallocate
TB
Go
-
Create a processed trigger (delete)
Create
Trigger
TR_DELETE
On
dbo.sendtab
For
Delete
AS
Declare
@ID
int
Declare
TB
Cursor
Local
For
SELECT
id
From
deleted
Open
TB
Fetch
TB
INTO
@ID
While
@@ fetch_status
=
0
Begin
EXEC
DBO.P_JOBSET
@ID
=
@ID
,
@is_delete
=
1
Fetch
TB
INTO
@ID
End
Close
TB
Deallocate
TB
Go
-
Test (after each step is done, you can see if Job is established, after time, you can see if the job is automatically deleted, and whether dbo.accetetab has a record)
-
a. Insert data
Insert
dbo.sendtab
SELECT
N
'
Instrument 1
'
,
Dateadd
(s,
1
,
GetDate
())
'
Unita
'
,
'
Unitb
'
N
'
TXT
'
Union
All
SELECT
N
'
Instrument 2
'
,
Dateadd
(D,
1
,
GetDate
())
'
Unita
'
,
'
Unitb
'
N
'
TXT
'
Union
All
SELECT
N
'
Instrument 3
'
,
Dateadd
(M,
1
,
GetDate
())
'
Unita
'
,
'
Unitb
'
N
'
TXT
'
-
b. Modify
Update
dbo.sendtab
Set
Name
=
N
'
Archive 1
'
Sendtime
=
Dateadd
(s,
5
,
GetDate
())
WHERE
id
=
2
-
C. Delete
Delete
dbo.sendtab
WHERE
id
=
3
Go
-
Delete test
Drop
TABLE
dbo.sendtab, dbo.accetab
Drop
PROC
DBO.P_JOBSET
