/*作业处理实例
根据sendTab的SendTime定制作业
并且在该作业完成时可以自动删除作业
*/
示例
测试表
create table sendTab(ID int identity()Name varchar()
SendTime datetimeAcceptUnit varchar()
SendUnit varchar()Content varchar())
create table accepteTab(ID int identity()Name varchar()
SendUnit varchar()AcceptUnit varchar()Content varchar())
go
创建处理的存储过程
create proc p_JobSet
@id int要处理的sendTab的id
@is_delete bit=是否仅删除为则否为则是
as
declare @dbname sysname@jobname sysname
@date int@time int
select @jobname=定时发送作业_+cast(@id as varchar)
@date=convert(varcharSendTime)
@time=replace(convert(varcharSendTime):)
from sendTab where id=@id
if exists(select from msdbsysjobs where name=@jobname)
exec msdbsp_delete_job @job_name=@jobname
if @is_delete= return
创建作业
exec msdbsp_add_job @job_name=@jobname@delete_level=
创建作业步骤
declare @sql varchar()
select @sql=insert accepteTab(nameSendUnitAcceptUnitContent)
select nameAcceptUnitSendUnitContent from sendTab where id=
+cast(@id as varchar)
@dbname=db_name()
exec msdbsp_add_jobstep @job_name=@jobname
@step_name = 发送处理步骤
@subsystem = TSQL
@database_name=@dbname
@command = @sql
@retry_attempts = 重试次数
@retry_interval = 重试间隔
创建调度
EXEC msdbsp_add_jobschedule @job_name = @jobname
@name = 时间安排
@enabled =
@freq_type =
@active_start_date = @date
@active_start_time = @time
添加目标服务器
EXEC msdbdbosp_add_jobserver
@job_name = @jobname
@server_name = N(local)
go
创建处理的触发器(新增/修改)
create trigger tr_insert_update on sendTab
for insertupdate
as
declare @id int
declare tb cursor local for select id from inserted
open tb
fetch next from tb into @id
while @@fetch_status=
begin
exec p_JobSet @id
fetch next from tb into @id
end
close tb
deallocate tb
go
创建处理的触发器(删除)
create trigger tr_delete on sendTab
for delete
as
declare @id int
declare tb cursor local for select id from deleted
open tb
fetch next from tb into @id
while @@fetch_status=
begin
exec p_JobSet @id
fetch next from tb into @id
end
close tb
deallocate tb
go
测试
插入数据
insert sendTab
select 文书// ::UnitAUnitBtxt
union all select 文书// ::UnitAUnitBtxt
union all select 文书// ::UnitAUnitBtxt
修改
update sendTab set name=档案SendTime=// ::
where id=
删除
delete sendtab where id=
go
删除测试
drop table sendTabaccepteTab
drop proc p_JobSet
