/**/
/*--作业处理实例
根据 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
根据 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