Saturday, September 27, 2008

Send mail from SQL SERVER 2005 after Insert Statement

The below stored procedure send a mail.

The profile name should be already created.

The Procedure that sends the mail is msdb.dbo.sp_send_dbmail

ALTER TRIGGER [MailOnError] ON [dbo].[LogMessages] AFTER
INSERT AS
DECLARE @LoggerId    INT
DECLARE @TableName   VARCHAR(75)
DECLARE @TableNameId INT
DECLARE @MessageId   INT
DECLARE @MessageText VARCHAR(1000)
DECLARE @LoggedTime  DATETIME
DECLARE @Body        VARCHAR(1100)
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        -- Insert statements for trigger here
        SELECT @LoggerId    = LoggerId   ,
               @TableName   = TableName  ,
               @TableNameId = TableNameId,
               @MessageId   = MessageId  ,
               @MessageText = MessageText,
               @LoggedTime  = LoggedTime
        FROM   LogError
        
        IF @MessageId = 3
        BEGIN
                SET @Body = 
                'LoggerId = ' + CAST( @LoggerId AS VARCHAR ) + ',   ' + 
                'TableName  = '+ @TableName + ', ' + 
                'TableNameId = ' + CAST( @TableNameId AS VARCHAR ) + ', ' + 
                'MessageId = ' + CAST( @MessageId AS VARCHAR ) + ', ' + 
                'MessageText = ' + @MessageText + ', ' + 
                'LoggedTime = '+ CAST( @LoggedTime AS VARCHAR )
                
                EXEC msdb.dbo.sp_send_dbmail
                        @profile_name = 'DBOException' ,
                        @recipients   = 'babukumarasamy@gmail.com' ,
                        @subject      = 'Exception occured!' ,
                        @body         = @Body,
                        @importance   = 'High',
                        @body_format  = 'HTML',
                        @sensitivity  = 'Confidential'
        END
END

Read the book
Pro SQL Server 2012 Administration By Ken Simmons, Sylvester Carstarphen
Page#: 326

No comments: