sql server 新生入学管理系统

alter table classes add constraint c1 foreign key (zno) references majors(zno) alter table majors add constraint c2 foreign key (xno) references sdepts(xno) alter table students add constraint c3 foreign key (bno) references classes(bno) alter table students add constraint c4 foreign key (qno) references rooms(qno) alter table students add constraint c5 unique (tel) alter table students add constraint c6 default '男' for ssex create view v_stu(学号,姓名,班级,专业,系部,寝室,性别) as select sno,sname,bname,zname,xname,qname,ssex from students,classes,majors,rooms,sdepts where students.bno=classes.bno and students.qno=rooms.qno and majors.xno=sdepts.xno and classes.zno=majors.zno create view v_tea(学号,姓名,班级,专业,系部,公寓,性别,年龄,政治面貌,籍贯,父亲姓名,母亲姓名,家庭联系电话) as select sno,sname,bname,zname,xname,qname,ssex,sage,zhengzhi,jiguan,fname,mname,tel from students,classes,majors,rooms,sdepts where students.bno=classes.bno and students.qno=rooms.qno and majors.xno=sdepts.xno and classes.zno=majors.zno create trigger t1 on students for update as if update(sno) begin print'禁止修改学生的学号' rollback end create trigger t2 on students for update,insert as if(((select ssex from inserted)='男')and(((select qno from inserted )='q02')or((select qno from inserted )='q04'))or((select ssex from inserted)='女')and(((select qno from inserted )='q01')or((select qno from inserted )='q03'))) begin print'宿舍分配出错，请重新分配！' rollback end --insert into students --values --('n0017','张建','jd1002',2009,'q02',19,'男','张晨','黄帆','15830463617','浙江','共青团员') CREATE PROC s1_info @sn char(10) AS SELECT sname,bname,zname FROM students,majors,classes WHERE students.bno=classes.bno and classes.zno=majors.zno and students.sno=@sn Go --EXEC s1_info 'n0001'