数据库触发器和作业结合讲解
文章作者 100test 发表时间 2007:08:06 13:21:07
来源 100Test.Com百考试题网
/*--作业处理实例
根据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_0delete bit=0 --是否仅删除,为0则否,为1则是
as
declare @dbname sysname,@jobname sysname
,@date int,@time int
0select @jobname= 定时发送作业_ cast(@id as varchar)
,@date=convert(varchar,SendTime,112)
,@time=replace(convert(varchar,SendTime,108), : , )
from sendTab where id=@id
if exists(0select 1 from msdb..sysjobs where name=@jobname)
exec msdb..sp_0delete_job @job_name=@jobname
if @is_0delete=1 return
--创建作业
exec msdb..sp_add_job @job_name=@jobname,@0delete_level=1
--创建作业步骤
declare @sql varchar(800)
0select @sql= insert accepteTab(name,SendUnit,AcceptUnit,Content)
0select 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_0update on sendTab
for insert,0update
as
declare @id int
declare tb cursor local for 0select 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_0delete on sendTab
for 0delete
as
declare @id int
declare tb cursor local for 0select id from 0deleted
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
0select 文书1 , 2004/5/1 12:00:00 , UnitA , UnitB , txt
union all 0select 文书2 , 2004/5/12 12:00:00 , UnitA , UnitB , txt
union all 0select 文书3 , 2004/5/21 12:00:00 , UnitA , UnitB , txt
--修改
0update sendTab set name= 档案1 ,SendTime= 2004/5/1 15:00:00
where id=1
--删除
0delete sendtab where id=3
go
--删除测试
0drop table sendTab,accepteTab
0drop proc p_JobSet
src="/oracle/js/wxgg_oracle.js">