熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

數據庫觸發器和作業結合講解

2013-11-13 15:56:18  來源: Oracle 

  /*作業處理實例

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


From:http://tw.wingwit.com/Article/program/Oracle/201311/17542.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.