/**/ /*--作业处理实例 根据 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,ContentFROM dbo.sendTabWHERE 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