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'级联删除成功'
题库管理系统(数据库设计部分)
最新推荐文章于 2024-01-21 06:15:00 发布