触发器和作业结合- -
/*--作业处理实例
根据sendTab的SendTime定制作业 并且在该作业完成时,可以自动删除作业 --*/
--示例
--测试表 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
|