When you create this trigger on the sysjobs table of msdb, it will send out an email alert whenever someone changes the state of the job from Enabled to Disabled or from Disabled state to Enabled.
For setting up database mail option and to use sp_send_dbmail, refer to my earlier post "Configuring Database Mail"
For setting up database mail option and to use sp_send_dbmail, refer to my earlier post "Configuring Database Mail"
USE msdb
GO
CREATE Trigger tr_AuditJobEnable
ON sysjobs  
FOR UPDATE 
AS
DECLARE @UserName VARCHAR(50),  
@HostName VARCHAR(50),  
@JobName VARCHAR(100),  
@DeletedJobName VARCHAR(100),  
@Ins_EnabledFlag INT,  
@Del_EnabledFlag INT,  
@Body VARCHAR(200),  
@Subject VARCHAR(200), 
@Servername VARCHAR(50) 
SELECT @UserName = SYSTEM_USER, @HostName = HOST_NAME()  
SELECT @Ins_EnabledFlag = Enabled FROM Inserted  
SELECT @Del_EnabledFlag = Enabled FROM Deleted  
SELECT @JobName = Name FROM Inserted  
SELECT @Servername = @@servername 
IF @Ins_EnabledFlag <> @Del_EnabledFlag  
BEGIN  
  IF @Ins_EnabledFlag = 1  
   BEGIN  
  SET @Body = 'The User "'+@username+'" from "'+@hostname+
   '" ENABLED the Job "'+@jobname+'" on '+CONVERT(VARCHAR(20),GETDATE(),100)  
  SET @Subject = 'SQL Job "'+@jobname+ '" on ' + @Servername+
   ' has been ENABLED at '+CONVERT(VARCHAR(20),GETDATE(),100)  
   END  
  IF @Ins_EnabledFlag = 0  
   BEGIN  
  SET @Body = 'The User "'+@username+'" from "'+@hostname+
   '" DISABLED the Job "'+@jobname+'" on '+CONVERT(VARCHAR(20),GETDATE(),100)  
  SET @Subject = 'SQL Job "'+@jobname+ '" on ' + @Servername+
   ' has been DISABLED at '+CONVERT(VARCHAR(20),GETDATE(),100)   
   END  
-- Send e-Mail 
Exec msdb..sp_send_dbmail
       @profile_name='DBA' -- Change to your Profile
      ,@recipients='segu.sandesh@gmail.com' -- Change Recipients
      ,@Subject=@Subject
      ,@Body=@Body
END
can u post upgaration & migration procedure...with step by step
ReplyDelete