重构，每天对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