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

 

/**//*--作业处理实例

    根据 sendTab 的 SendTime 定制作业
    并且在该作业完成时,可以自动删除作业
--邹建 2004.04(引用请保留此信息)--*/

--示例

--测试表
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