题库管理系统（数据库设计部分）

Create table teacher
(
	T_Account char(20) not null,
	T_Password char(15) not null,
	T_Name char(10),
	T_Sex char(2),
	T_Education char(16),
	T_CreTime smalldatetime,
	T_MgmName char(15)
	constraint uq_t_account unique (T_Account),
	constraint ck_t_sex check (T_Sex='男' or T_Sex='女'),
	constraint fk_t_mgmName foreign key(T_MgmName) references management(M_Account)
)

//为Subject建立自增加主键
ALTER TABLE subject DROP COLUMN S_No 
ALTER TABLE subject 
ADD 
S_No int identity(1,1) not null PRIMARY KE

//建立用户（管理员+教师）视图
Create view AllAccount
as
select m_account as all_account
from management
union
select t_account
from teacher


Create Procedure pr_RetManData
(
	@row  int
)
as 
begin
	Declare cursor_Man SCROLL CURSOR
	for 
	select *
	from management
	Declare @m_acc	char(20)
	Declare @m_pas	char(30)
	Declare @m_comp	int
	Declare @m_time	smalldatetime
	Open cursor_Man
	--Fetch ABSOLUTE @row from cursor_Man
	Fetch ABSOLUTE @row from cursor_Man
	into @m_acc,@m_pas,@m_comp,@m_time

	select *
	from management
	where m_account=@m_acc
	Close cursor_Man
	Deallocate cursor_Man
End

//分值限定在0~100的插入触发器
create trigger  sub_insert_tri
on subject
for insert
as
Declare @val int 
select @val=Inserted.S_Value
from Inserted

if @val<=0 or @val>100
begin
	Rollback Transaction
	Print '分值必须大于0，小于等于100'
end


//删除TEACHER事务
Create Trigger delMan
on management
for delete
As
Declare @row  int
select @row=@@ROWCOUNT
if @row=0
Begin
	print'没有删除记录'
	return
End 
if @row>1
Begin
	rollback Transaction
	raiserror('您一次只能删除一条记录',16,10)
	return 
End
--获取删除的管理员号
DECLARE @m_acc  char(20)
select @m_acc=m_account
from Deleted
--删除该管理员创建的所有教师
DELETE
from teacher
where T_MgmName=@m_acc
print'级联删除成功'