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