根据表中记录的变化情况自动维护作业

 

/*--作业处理实例

    根据 sendTab 的 SendTime 定制作业
    并且在该作业完成时,可以自动删除作业*/

-- 示例

-- 测试表
CREATE   TABLE  dbo.sendTab(
    ID  int   IDENTITY ( 1 , 1 ),
    Name  nvarchar ( 10 ),
    SendTime  datetime ,
    AcceptUnit  varchar ( 10 ),
    SendUnit  varchar ( 10 ),
    Content  nvarchar ( 4000 )
)

CREATE   TABLE  dbo.accepteTab(
    ID  int   IDENTITY ( 1 , 1 ),
    Name  nvarchar ( 10 ),
    SendUnit  varchar ( 10 ),
    AcceptUnit  varchar ( 10 ),
    Content  nvarchar ( 4000 )
)
GO

-- 创建处理的存储过程
CREATE   PROC  dbo.p_JobSet
     @id   int ,             -- 要处理的sendTab的id
     @is_delete   bit = 0      -- 是否仅删除,为0则否,为1则是
AS
DECLARE
     @dbname  sysname,
     @jobname  sysname,
     @date   int ,
     @time   int

--  job 名称及运行时间
SELECT  
     @jobname   =  N ' 定时发送作业_ '   +   CAST ( @id   as   nvarchar ),
     @date   =   CONVERT ( varchar , SendTime,  112 ),
     @time   =   REPLACE ( CONVERT ( varchar , SendTime,  108 ),  ' : ' ,  '' )
FROM  dbo.sendTab
WHERE  id  =   @id

--  如果 job 已经存在, 则删除
IF   EXISTS (
         SELECT   1   FROM  msdb.dbo.sysjobs
         WHERE  name  =   @jobname )
     EXEC  msdb.dbo.sp_delete_job
             @job_name   =   @jobname  

IF   @is_delete   =   1
     RETURN

--  创建job
EXEC  msdb.dbo.sp_add_job
     @job_name   =   @jobname ,
     @delete_level   =   1

--  创建 job 步骤
DECLARE
     @sql   varchar ( 800 )
SELECT
     @sql   =  N '  -- job 要实现的操作
INSERT dbo.accepteTab(
    name,SendUnit,AcceptUnit,Content)
SELECT
    name,AcceptUnit,SendUnit,Content
FROM dbo.sendTab
WHERE id =  '  
         +   CAST ( @id   as   varchar ),
     @dbname   =   DB_NAME ()

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

--  创建调度
EXEC  msdb.dbo.sp_add_jobschedule
     @job_name   =   @jobname , 
     @name   =  N ' 时间安排 ' ,
     @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  dbo.sendTab
FOR   INSERT , UPDATE
AS
DECLARE  
     @id   int
DECLARE  tb  CURSOR  LOCAL
FOR
SELECT
    id
FROM  inserted
OPEN  tb
FETCH  tb  INTO   @id
while   @@fetch_status = 0
BEGIN
     EXEC  dbo.p_JobSet
         @id   =   @id
    
     FETCH  tb  INTO   @id
END
CLOSE  tb
DEALLOCATE  tb
go

--  创建处理的触发器(删除)
CREATE   TRIGGER  tr_delete
     ON  dbo.sendTab
FOR   DELETE
AS
DECLARE
     @id   int
DECLARE  tb  CURSOR  LOCAL
FOR
SELECT
    id
FROM  deleted
OPEN  tb
FETCH  tb  INTO   @id
WHILE   @@FETCH_STATUS = 0
BEGIN
     EXEC  dbo.p_JobSet
         @id   =   @id ,
         @is_delete   =   1

     FETCH  tb  INTO   @id
END
CLOSE  tb
DEALLOCATE  tb
go

--  测试(每个步骤做完后, 可以看看 job 是否建立, 在时间到之后, 可以看看 Job 是否被自动删除, 并且 dbo.accepteTab 是否有记录)

--  a. 插入数据
INSERT  dbo.sendTab
SELECT  N ' 文书1 ' ,  DATEADD (s,  1 ,  GETDATE ()),  ' UnitA ' ,  ' UnitB ' , N ' txt '   UNION   ALL
SELECT  N ' 文书2 ' ,  DATEADD (d,  1 ,  GETDATE ()),  ' UnitA ' ,  ' UnitB ' , N ' txt '   UNION   ALL
SELECT  N ' 文书3 ' ,  DATEADD (m,  1 ,  GETDATE ()),  ' UnitA ' ,  ' UnitB ' , N ' txt '

--  b. 修改
UPDATE  dbo.sendTab  SET
    name  =  N ' 档案1 ' ,
    SendTime  =   DATEADD (s,  5 ,  GETDATE ())
WHERE  id  =   2

--  c. 删除
DELETE  dbo.sendtab
WHERE  id  =   3
GO

--  删除测试
DROP   TABLE  dbo.sendTab, dbo.accepteTab
DROP   PROC  dbo.p_JobSet