根据表中记录的变化情况自动维护作业

/**//*--作业处理实例

根据sendTab的SendTime定制作业
并且在该作业完成时,可以自动删除作业
--邹建2004.04(引用请保留此信息)--*/

--示例

--测试表
CREATETABLEdbo.sendTab(
IDintIDENTITY(1,1),
Namenvarchar(10),
SendTimedatetime,
AcceptUnitvarchar(10),
SendUnitvarchar(10),
Contentnvarchar(4000)
)

CREATETABLEdbo.accepteTab(
IDintIDENTITY(1,1),
Namenvarchar(10),
SendUnitvarchar(10),
AcceptUnitvarchar(10),
Contentnvarchar(4000)
)
GO

--创建处理的存储过程
CREATEPROCdbo.p_JobSet
@idint,--要处理的sendTab的id
@is_deletebit=0--是否仅删除,为0则否,为1则是
AS
DECLARE
@dbnamesysname,
@jobnamesysname,
@dateint,
@timeint

--job名称及运行时间
SELECT
@jobname=N'定时发送作业_'+CAST(@idasnvarchar),
@date=CONVERT(varchar,SendTime,112),
@time=REPLACE(CONVERT(varchar,SendTime,108),':','')
FROMdbo.sendTab
WHEREid=@id

--如果job已经存在,则删除
IFEXISTS(
SELECT1FROMmsdb.dbo.sysjobs
WHEREname=@jobname)
EXECmsdb.dbo.sp_delete_job
@job_name=@jobname

IF@is_delete=1
RETURN

--创建job
EXECmsdb.dbo.sp_add_job
@job_name=@jobname,
@delete_level=1

--创建job步骤
DECLARE
@sqlvarchar(800)
SELECT
@sql=N'--job要实现的操作
INSERTdbo.accepteTab(
name,SendUnit,AcceptUnit,Content)
SELECT
name,AcceptUnit,SendUnit,Content
FROMdbo.sendTab
WHEREid='
+CAST(@idasvarchar),
@dbname=DB_NAME()

EXECmsdb.dbo.sp_add_jobstep
@job_name=@jobname,
@step_name=N'发送处理步骤',
@subsystem='TSQL',
@database_name=@dbname,
@command=@sql,
@retry_attempts=5,--重试次数
@retry_interval=5--重试间隔

--创建调度
EXECmsdb.dbo.sp_add_jobschedule
@job_name=@jobname,
@name=N'时间安排',
@enabled=1,
@freq_type=1,
@active_start_date=@date,
@active_start_time=@time

--添加目标服务器
EXECmsdb.dbo.sp_add_jobserver
@job_name=@jobname,
@server_name=N'(local)'
go

--创建处理的触发器(新增/修改)
CREATETRIGGERtr_insert_update
ONdbo.sendTab
FORINSERT,UPDATE
AS
DECLARE
@idint
DECLAREtbCURSORLOCAL
FOR
SELECT
id
FROMinserted
OPENtb
FETCHtbINTO@id
while@@fetch_status=0
BEGIN
EXECdbo.p_JobSet
@id=@id

FETCHtbINTO@id
END
CLOSEtb
DEALLOCATEtb
go

--创建处理的触发器(删除)
CREATETRIGGERtr_delete
ONdbo.sendTab
FORDELETE
AS
DECLARE
@idint
DECLAREtbCURSORLOCAL
FOR
SELECT
id
FROMdeleted
OPENtb
FETCHtbINTO@id
WHILE@@FETCH_STATUS=0
BEGIN
EXECdbo.p_JobSet
@id=@id,
@is_delete=1

FETCHtbINTO@id
END
CLOSEtb
DEALLOCATEtb
go

--测试(每个步骤做完后,可以看看job是否建立,在时间到之后,可以看看Job是否被自动删除,并且dbo.accepteTab是否有记录)

--a.插入数据
INSERTdbo.sendTab
SELECTN'文书1',DATEADD(s,1,GETDATE()),'UnitA','UnitB',N'txt'UNIONALL
SELECTN'文书2',DATEADD(d,1,GETDATE()),'UnitA','UnitB',N'txt'UNIONALL
SELECTN'文书3',DATEADD(m,1,GETDATE()),'UnitA','UnitB',N'txt'

--b.修改
UPDATEdbo.sendTabSET
name=N'档案1',
SendTime=DATEADD(s,5,GETDATE())
WHEREid=2

--c.删除
DELETEdbo.sendtab
WHEREid=3
GO

--删除测试
DROPTABLEdbo.sendTab,dbo.accepteTab
DROPPROCdbo.p_JobSet