重构可让SQL提高可维护性，可读性以及效能性

重构，每天对Insus.NET来说，是必须的工作，不过一直是着重C#程序。昨晚在家中修改SQL一个小问题时，无意中看到一个触发器虽然它能正确运行，但值得重构它。

这个触发器功能是当用户预定会议室成功之后，系统会发送一封预定成功的确认函邮件。下面代码是原始触发器

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Insus.NET
-- Create date:   2011-08-07
-- Description:   Meeting Room Booking success notify
-- =============================================

CREATE TRIGGER [dbo].[tri_MeetingRoomBooking_BookingSuccessNotify]
ON [dbo].[MeetingRoomBooking]
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @MeetingRoomBookingId INT, @ContactEmail NVARCHAR(150),@CreateBy INT,@ContentPagePath NVARCHAR(1000)    
    
    --从INSERTED内存虚拟表给变量赋值    
    SELECT @MeetingRoomBookingId = [MeetingRoomBookingId],@ContactEmail = [Email],@CreateBy = [CreateBy] FROM INSERTED
    
    --设定邮件Web地址
    SET @ContentPagePath = N'http://www.yourcompany.com/MailNotify/BookingSuccess.aspx?ID=' + CONVERT(NVARCHAR(100),@MeetingRoomBookingId)
    
    --从Users表中找到预定人的邮箱
    DECLARE @CreaterEmail NVARCHAR(200)
    SELECT @CreaterEmail = [Email] FROM [dbo].[Users] WHERE [UsersId] = @CreateBy        
    
    --判断预定人与会议联系人是否为同一个人，还是帮助别人预定，并把相关的信息插入邮件发送列表中。
    IF (@ContactEmail = @CreaterEmail)    
        EXECUTE [dbo].[usp_MailingList_Insert] @Email = @ContactEmail,@Subject = N'会议室预定成功确认函',@HtmlPagePath = @ContentPagePath,@EmailParameterId = 2;
    ELSE
    BEGIN
        EXECUTE [dbo].[usp_MailingList_Insert] @Email = @ContactEmail,@Subject = N'会议室预定成功确认函',@HtmlPagePath = @ContentPagePath,@EmailParameterId = 2;
        EXECUTE [dbo].[usp_MailingList_Insert] @Email = @CreaterEmail,@Subject = N'会议室预定成功确认函',@HtmlPagePath = @ContentPagePath,@EmailParameterId = 2;
    END
END

 

重构过程以一些想法，

变量@MeetingRoomBookingId宣告的数据类型由INT改为NVARCHAR(100)，这样在设置邮件Web地址时，无需对CONVERT(NVARCHAR(100),@MeetingRoomBookingId)
转换了：......aspx?ID=' + @MeetingRoomBookingId

 

从Users表中找到预定人的邮箱，是先宣告变量，由于是使用SQL2008，可以在宣告变量之后，马上赋值： 

DECLARE @CreaterEmail NVARCHAR(200) = (SELECT [Email] FROM [dbo].[Users] WHERE [UsersId] = @CreateBy)

 

邮件标题，可以重构放入一个变量之中，某一天需要更改它时，只改一次即可。

@Subject NVARCHAR(50) = N'会议室预定成功确认函'

 

接下是此次重构的重点是判断预定人与会议联系人是否为同一个人，还帮助别人预定，并把相关的信息插入邮件发送列表中。正常判断如原始代码写法一样，当同一个人时，直接发送给预定人就行了，如果不是同一个人，要把预定成功的邮件发送给预定人和会议联系人。

此次，Insus.NET使用了反方式判断，判断由相等，改为不相等，减少IF的层次和可以节省一代码以及提高可维护性：

IF (@ContactEmail <> @CreaterEmail)    
    EXECUTE [dbo].[usp_MailingList_Insert] @Email = @CreaterEmail,@Subject = @Subject,@HtmlPagePath = @ContentPagePath,@EmailParameterId = 2;
        
EXECUTE [dbo].[usp_MailingList_Insert] @Email = @ContactEmail,@Subject = @Subject,@HtmlPagePath = @ContentPagePath,@EmailParameterId = 2;

 

重构之后，完整代码：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Insus.NET
-- Create date:   2011-08-07
-- Description:   Meeting Room Booking success notify
-- =============================================

ALTER TRIGGER [dbo].[tri_MeetingRoomBooking_BookingSuccessNotify]
ON [dbo].[MeetingRoomBooking]
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @MeetingRoomBookingId NVARCHAR(100), @ContactEmail NVARCHAR(150),@Subject NVARCHAR(50) = N'会议室预定成功确认函',@CreateBy INT,@ContentPagePath NVARCHAR(1000)    
    
    --从INSERTED内存虚拟表给变量赋值    
    SELECT @MeetingRoomBookingId = [MeetingRoomBookingId],@ContactEmail = [Email],@CreateBy = [CreateBy] FROM INSERTED
    
    --设定邮件Web地址
    SET @ContentPagePath = N'http://www.yourcompany.com/MailNotify/BookingSuccess.aspx?ID=' + @MeetingRoomBookingId
    
    --从Users表中找到预定人的邮箱
    DECLARE @CreaterEmail NVARCHAR(200) = (SELECT [Email] FROM [dbo].[Users] WHERE [UsersId] = @CreateBy)    
    
    --判断预定人与会议联系人是否为同一个人，还是帮助别人预定，并把相关的信息插入邮件发送列表中。    
    IF (@ContactEmail <> @CreaterEmail)    
        EXECUTE [dbo].[usp_MailingList_Insert] @Email = @CreaterEmail,@Subject = @Subject,@HtmlPagePath = @ContentPagePath,@EmailParameterId = 2;
        
    EXECUTE [dbo].[usp_MailingList_Insert] @Email = @ContactEmail,@Subject = @Subject,@HtmlPagePath = @ContentPagePath,@EmailParameterId = 2;
END