12 个解决方案
#1
写个公式
#2
直接用计算列
#3
因为我有一张学生信息表,包含学号、姓名等。还有一张登录信息表。想让学生信息表插入记录时,引发触发器,登录信息表也插入一条数据,帐号等于学号,密码初始值也为学号。以后可以修改的。
#4
如果另一个字段空呢
#5
嗯,可以用触发器
#6
另一个字段设置的非空的。
#7
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;
#8
你这个需求不是默认值的问题
#9
1.顺便请问下密码字段应该用什么数据类型?
2.密码长度8~20位如何限制密码长度?
2.密码长度8~20位如何限制密码长度?
#10
1.建议varchar或nvarchar
2.可以在程序语言上限制,也可以在SQL用check约束或触发器限制。
#11
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');
#12
非常感谢!
#1
写个公式
#2
直接用计算列
#3
因为我有一张学生信息表,包含学号、姓名等。还有一张登录信息表。想让学生信息表插入记录时,引发触发器,登录信息表也插入一条数据,帐号等于学号,密码初始值也为学号。以后可以修改的。
#4
如果另一个字段空呢
#5
嗯,可以用触发器
#6
另一个字段设置的非空的。
#7
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;
#8
你这个需求不是默认值的问题
#9
1.顺便请问下密码字段应该用什么数据类型?
2.密码长度8~20位如何限制密码长度?
2.密码长度8~20位如何限制密码长度?
#10
1.建议varchar或nvarchar
2.可以在程序语言上限制,也可以在SQL用check约束或触发器限制。
#11
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');
#12
非常感谢!