开发的软件中是允许用户可以更自己的密码,现在客户有这样一个要求,是当用户更新密码时,要限制用户不能重新使用最近三次的历史密码。
实现客户的要求,唯有在数据库,创建一个密码更新Log表,然后再为Member表写一个触发器,每当用户更新密码时,去检索这个Log表,看看这次更新的密码是否存在Log表中,如果存在,提示用户,返之继续更新动作,然后把更新的信息插入Log表中,最后是删除Log记录,只保留此用户最近三次记录。
相关代码,可以参考:
MemberPasswordChangeLog
CREATE
TABLE MemberPasswordChangeLog
(
Log_Id INT IDENTITY( 1, 1) PRIMARY KEY NOT NULL,
MemberId INT NOT NULL,
PWD_Value NVARCHAR( 100) NOT NULL,
Log_Date DATETIME DEFAULT( GETDATE()) NOT NULL
)
GO
(
Log_Id INT IDENTITY( 1, 1) PRIMARY KEY NOT NULL,
MemberId INT NOT NULL,
PWD_Value NVARCHAR( 100) NOT NULL,
Log_Date DATETIME DEFAULT( GETDATE()) NOT NULL
)
GO
触发器:
tri_Member_Update
CREATE
TRIGGER
[
dbo
].
[
tri_Member_Update
]
ON [ dbo ]. [ Member ]
FOR UPDATE
AS
BEGIN
DECLARE @MemberId INT, @N_Value NVARCHAR( 100), @O_Value NVARCHAR( 100)
SELECT @MemberId = [ MemberId ], @O_Value = [ Password ] FROM DELETED
SELECT @O_Value = [ Password ] FROM INSERTED
IF @N_Value <> @O_Value -- 比较新旧两个值,如果不一样,会员更新密码
BEGIN
-- 去检查MemberPasswordChangeLog表,看看最近用户更新密码的情况
IF EXISTS( SELECT TOP 1 1 FROM [ dbo ]. [ MemberPasswordChangeLog ] WHERE [ MemberId ] = @MemberId AND [ PWD_Value ] = @N_Value)
BEGIN
RAISERROR(N ' 不能使用最近三次的历史密码。 ', 16, 1)
RETURN
END
-- 把更新数据插入Log表中
INSERT INTO [ dbo ]. [ MemberPasswordChangeLog ] ( [ MemberId ], [ PWD_Value ]) VALUES ( @MemberId, @N_Value)
-- 保留最近更新三笔记录,删除额外记录
DELETE FROM [ dbo ]. [ MemberPasswordChangeLog ] WHERE [ MemberId ] = @MemberId AND [ Log_Id ] NOT IN (
SELECT TOP( 3) [ Log_Id ] FROM [ dbo ]. [ MemberPasswordChangeLog ] WHERE [ MemberId ] = @MemberId ORDER BY [ Log_Date ] DESC
)
END
END
ON [ dbo ]. [ Member ]
FOR UPDATE
AS
BEGIN
DECLARE @MemberId INT, @N_Value NVARCHAR( 100), @O_Value NVARCHAR( 100)
SELECT @MemberId = [ MemberId ], @O_Value = [ Password ] FROM DELETED
SELECT @O_Value = [ Password ] FROM INSERTED
IF @N_Value <> @O_Value -- 比较新旧两个值,如果不一样,会员更新密码
BEGIN
-- 去检查MemberPasswordChangeLog表,看看最近用户更新密码的情况
IF EXISTS( SELECT TOP 1 1 FROM [ dbo ]. [ MemberPasswordChangeLog ] WHERE [ MemberId ] = @MemberId AND [ PWD_Value ] = @N_Value)
BEGIN
RAISERROR(N ' 不能使用最近三次的历史密码。 ', 16, 1)
RETURN
END
-- 把更新数据插入Log表中
INSERT INTO [ dbo ]. [ MemberPasswordChangeLog ] ( [ MemberId ], [ PWD_Value ]) VALUES ( @MemberId, @N_Value)
-- 保留最近更新三笔记录,删除额外记录
DELETE FROM [ dbo ]. [ MemberPasswordChangeLog ] WHERE [ MemberId ] = @MemberId AND [ Log_Id ] NOT IN (
SELECT TOP( 3) [ Log_Id ] FROM [ dbo ]. [ MemberPasswordChangeLog ] WHERE [ MemberId ] = @MemberId ORDER BY [ Log_Date ] DESC
)
END
END