zjcxc(邹建)的Blog - SQL Server

引用或者转载本BLOG的文章请注明原作者和出处,并保留原文章中的版权信息。谢谢!...

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

 

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

    根据 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, 1GETDATE()), 'UnitA''UnitB', N'txt' UNION ALL
SELECT N'文书2'DATEADD(d, 1GETDATE()), 'UnitA''UnitB', N'txt' UNION ALL
SELECT N'文书3'DATEADD(m, 1GETDATE()), 'UnitA''UnitB', N'txt'

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

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

-- 删除测试
DROP TABLE dbo.sendTab, dbo.accepteTab
DROP PROC dbo.p_JobSet
阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭