/*--作業處理實例
根據 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
