34,590
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('students') is not null
drop table students;
go
create table students (student_id char(13) primary key,name varchar(20) not null);
go
if OBJECT_ID('accounts') is not null
drop table accounts;
go
create table accounts (account_id char(13) primary key,passwd binary(20) not null);
go
create trigger trg_students_ins on students
for insert
as
set nocount on;
insert into accounts (account_id, passwd)
select student_id,hashbytes('sha1',student_id) from inserted;
go
insert into students values('S2010F0402001','Wang Fang');
select * from students;
select * from accounts;
if OBJECT_ID('students') is not null
drop table students;
go
create table students (student_id char(13) primary key,name varchar(20) not null);
go
if OBJECT_ID('accounts') is not null
drop table accounts;
go
create table accounts (account_id char(13) primary key,passwd varbinary(20) not null);
go
create trigger trg_students_ins on students
for insert
as
set nocount on;
insert into accounts (account_id, passwd)
select student_id,cast(student_id as varbinary(20)) from inserted;
go
create trigger trg_accounts_pwd on accounts
for insert, update
as
set nocount on;
-- 判断密码的长度
if exists (select * from inserted where datalength(passwd) not between 8 and 20)
begin
raiserror('Invalid password.',15,0);
rollback tran;
end
-- 基于安全考虑,存储密码的 hash 值,而不直接存储密码原文。
update a set a.passwd=hashbytes('sha1',a.passwd)
from accounts a,inserted i where a.account_id=i.account_id;
go
insert into students values('S2010F0402001','Wang Fang');
-- 允许
update accounts set passwd=cast('p@ssw0rd' as varbinary(20));
-- 违反
update accounts set passwd=CAST('123456' as varbinary(20));
select * from students;
-- 通过将密码原文转为 hash 值验证密码
select * from accounts where passwd=hashbytes('sha1','p@ssw0rd');