重构可让SQL提高可维护性，可读性以及效能性
重构，每天对Insus.NET来说，是必须的工作，不过一直是着重C#程序。昨晚在家中修改SQL一个小问题时，无意中看到一个触发器虽然它能正确运行，但值得重构它。
这个触发器功能是当用户预定会议室成功之后，系统会发送一封预定成功的确认函邮件。下面代码是原始触发器
tri_MeetingRoomBooking_BookingSuccessNotify
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;
重构之后，完整代码：
tri_MeetingRoomBooking_BookingSuccessNotify
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
