USE [premiere]
GO
/****** Object:  Trigger [dbo].[TestupdCreditLimit]    Script Date: 03/10/2010 20:25:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* =============================================
   Author:		Prabakar
   Create date: 3/9/10
   Description:	Trigger for Update operation in Customer table
     and illustrates the use of "msg (net send)" and "send mail" features
   By default, the xp_cmdshell command is disabled in the SQL server.
   This feature must be enabled one time as below in a query window.
   Subsequently, the extended procedure xp_cmdshell can be used.
     -- To allow advanced options to be changed.
     EXEC sp_configure 'show advanced options', 1
     GO
     -- To update the currently configured value for advanced options.
     RECONFIGURE
     GO
     -- To enable the feature.
     EXEC sp_configure 'xp_cmdshell', 1
     GO
     -- To update the currently configured value for this feature.
     RECONFIGURE
     GO

   ============================================= */
ALTER TRIGGER [dbo].[TestupdCreditLimit] 
   ON  [dbo].[Customer] 
   AFTER UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    Declare @CustomerNr char(3);
    Declare @PrevCLimit Decimal(8,2);
    Declare @NextCLimit Decimal(8,2);
    Declare @ChangeCLimit Decimal(8,2);
    Declare @Message Varchar(100);
    Declare @Heading Varchar(60);
    declare @myPopupCmd varchar(80);

    -- Insert statements for trigger here
    -- Identify the pair of rows from DELETED & INSERTED for the update operation
    select @CustomerNr = d.CustomerNum, @PrevCLimit = d.CreditLimit,
	    @NextCLimit = i.CreditLimit
    from DELETED d, INSERTED i
    where d.CustomerNum = i.CustomerNum;

    IF (@NextCLimit < 0.0) -- check for negative credit limit value & prevent it
    BEGIN
        -- pop a window with an error message
		set @myPopupCmd = 'msg ' + SYSTEM_USER + ' "Negative credit limit value is not permitted" '
		EXEC xp_cmdshell @myPopupCmd, no_output
		print 'Update on customer is invalid.' +
         	' Credit Limit cannot be negative for the customer ' + @CustomerNr;
        Rollback Transaction	
    END
    Else
      Begin
        set @ChangeCLimit = ABS(@NextCLimit - @PrevCLimit);

        If (@ChangeCLimit > (@PrevCLimit / 2.0))
         BEGIN -- Credit limit value was changed more than 50%
          -- notify the responsible party through an email
	      set @message = 'The credit limit value for the customer ' + @CustomerNr +
	          ' is changed from ' + cast(@PrevCLimit as varchar(10)) + ' to ' +
	      cast(@NextCLimit as varchar(10));
          set @Heading = 'Customer ' + @CustomerNr +
	          ' credit limit update - abnormal';
          EXEC msdb.dbo.sp_send_dbmail
	      @profile_name = 'snook01',
	      @recipients = 'GemMine@gmail.com',
          @subject = @Heading,
          @body = @Message,
          @body_format = 'HTML' ;
          print 'An email is sent to warn the unusual credit limit change' +
       		' for the customer ' + @CustomerNr;
	      -- Rollback Transaction
        END
     END
END
