SQL语句创建SQL作业

/*--作业处理实例

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

--示例

--测试表
create table sendTab(ID int identity(1,1),Name varchar(10)
    ,SendTime datetime,AcceptUnit varchar(10)
    ,SendUnit varchar(10),Content varchar(8000))

create table accepteTab(ID int identity(1,1),Name varchar(10)
    ,SendUnit varchar(10),AcceptUnit varchar(10),Content varchar(8000))
go

--创建处理的存储过程
create proc p_JobSet
@id int,            --要处理的sendTab的id
@is_delete bit=0    --是否仅删除,为0则否,为1则是
as
declare @dbname sysname,@jobname sysname
    ,@date int,@time int

select @jobname='定时发送作业_'+cast(@id as varchar)
    ,@date=convert(varchar,SendTime,112)
    ,@time=replace(convert(varchar,SendTime,108),':','')
from sendTab where id=@id

if exists(select 1 from msdb..sysjobs where name=@jobname)
    exec msdb..sp_delete_job @job_name=@jobname

if @is_delete=1 return

--创建作业
exec msdb..sp_add_job @job_name=@jobname,@delete_level=1

--创建作业步骤
declare @sql varchar(800)
select @sql='insert accepteTab(name,SendUnit,AcceptUnit,Content)
        select name,AcceptUnit,SendUnit,Content from sendTab where id='
        +cast(@id as varchar)
    ,@dbname=db_name()

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

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

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

--创建处理的触发器(新增/修改)
create trigger tr_insert_update on sendTab
for insert,update
as
declare @id int
declare tb cursor local for select id from inserted
open tb
fetch next from tb into @id
while @@fetch_status=0
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=0
begin
    exec p_JobSet @id,1
    fetch next from tb into @id
end
close tb
deallocate tb
go

--测试

--插入数据
insert sendTab
select '文书1','2004/5/1 12:00:00','UnitA','UnitB','txt'
union all select '文书2','2004/5/12 12:00:00','UnitA','UnitB','txt'
union all select '文书3','2004/5/21 12:00:00','UnitA','UnitB','txt'

--修改
update sendTab set name='档案1',SendTime='2004/5/1 15:00:00'
where id=1

--删除
delete sendtab where id=3
go
--删除测试
drop table sendTab,accepteTab
drop proc p_JobSet