/ * - Work processing instance
• According to Sendtab's SendTime custom job? And when the job is completed, you can automatically delete your job - * /
- Example
- Test Table Create Table Sendtab (ID Int Id Id Intity (1), Name VARCHAR (10)?, Sendtime DateTime, AcceptUnit Varchar (10)?, Sendunit Varchar (10), Content Varchar (8000))
Create Table Accetab (ID Int Id Id Intity (1), Name VARCHAR (10)?, Sendunit Varchar (10), AcceptUnit Varchar (10), Content Varchar (8000)) Go
- Create a process of stored procedure create proc p_jobset @ ID int , @ JobName sysname?, @ Date Int, @ Time Int
SELECT @ JobName = 'Timed Send Job_' Cast (@ID as varchar)?, @ Date = Convert (varchar, sendtime, 112)?, @ Time = Replace (Convert (varchar, sendtime, 108), ':' , '') from sendtab where id = @ id
If EXISTS (SELECT 1 from msdb..sysjobs where name = @ JobName)? EXEC MSDB..SP_DELETE_JOB @ Job_Name = @ JobName
IF @ is_delete = 1 return
- Create Jobs EXEC MSDB..SP_ADD_JOB @ Job_Name = @ JobName, @ delete_level = 1
- Create Job Step Declare @SQL VARCHAR (800) SELECT @ SQL = 'INSERT Acceptab (Name, Sendunit, AcceptUnit, Content) ?? Select Name, AcceptUnit, Sendunit, Content from sendtab where id =' ?? cast (@ ID as varchar)?, @ dbname = db_name ()
exec msdb..sp_add_jobstep @ job_name = @ jobname,? @ step_name = 'transmission processing',? @ subsystem = 'TSQL',? @ database_name = @ dbname,? @ command = @sql,? @ retry_attempts = 5,? ? - Retry the number of times? @Retry_interval = 5??? - Retry interval
- Create a schedule EXEC msdb..sp_add_jobschedule @job_name = @jobname, @name = 'schedule', @ enabled = 1, @freq_type = 1, @active_start_date = @date, @ active_start_time = @ time-????? - Add a target server exec msdb.dbo.sp_add_jobserver? @JOB_NAME = @JobName,? @ Server_name = n '(local)' Go
- trigger creation process (add / modify) create trigger tr_insert_update on sendTabfor insert, updateasdeclare @id intdeclare tb cursor local for select id from insertedopen tbfetch next from tb into @idwhile @@ fetch_status = 0begin exec p_JobSet @id?? Fetch next from tb @idendclose TBDEAllocate TBGO
-? Creation process triggers (deletion) create trigger tr_delete on sendTabfor deleteasdeclare @id intdeclare tb cursor local for select id from deletedopen tbfetch next from tb into @idwhile @@ fetch_status = 0begin exec p_JobSet @ id, 1 fetch next from? TB INTO @idendClose TBDEAllocate TBGO
--test
- Insert data insert sendtabselect 'instrument 1', '2004/5/1 12:00:00', 'Unita', 'unitb', 'txt'Union all select' document 2 ',' 2004/5/12 12 : 00: 00 ',' Unita ',' Unitb ',' TXT'Union All Select 'Tender 3', '2004/5/21 12:00:00', 'Unita', 'Unitb', 'TXT'
- Modify Update Sendtab Set Name = 'Archive 1', SendTime = '2004/5/1 15: 00: 00where ID = 1
- Delete delete sendtab where id = 3GO - Remove Table SENDTAB, AcceptAbDrop Proc P_Jobset
