/*--作業處理實例

	根據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