杭电数据库张红娟编第4章习题参考答案.doc

/* 完成表中约束的定义 */-创建course表create table course(cno char(1) primary key,cname varchar(20) not null,credit smallint check（credit=1 and credit1 and number14 and sage0 and gmark 100), primary key (sno,cno)/* 针对成绩管理数据库中的表，完成以下操作：*/-（1）用户张勇对Student表和Course表有Select权力。Grant select on student to 张勇Grant select on course to 张勇-（2）把对表Student的INSERT和Delete权限授予用户张三，并允许他再把此权限授予其他用户。Grant insert,delete on student to 张三 with grant option-（3）把查询Course表和修改属性Credit的权限授给用户李四。Grant select,update(credit) on course to 李四-（4）授予用户李勇敏对Student表的所有权力（读、插、删、改），并具有给其他用户授权的权力。Grant all privilege on student to 李勇敏 with grant option-（5）撤销（1）中对张勇所授予的所有权力。Revoke select on student to 张勇Revoke select on course to 张勇或：Revoke select on student from 张勇Revoke select on course from 张勇-（6）撤销（2）中对张三所授予的所有权力。revoke insert,delete on student to 张三cascade或revoke insert,delete on student from 张三cascade/* 为成绩管理数据库中的Student表创建一触发器：当向表中插入或删除记录时，修改Class表中相应班级的人数。*/-创建insert触发器，适用于student表的单行数据的添加create trigger stu_inserton studentafter insertas update class set number=number+1 from class,inserted where class.clno = inserted.clno-创建delete触发器，适用于student表的单行数据的删除create trigger stu_deleteon studentafter deleteas update class set number=number-1 from class,deleted where class.clno = deleted.clno -将insert和delete写入一个触发器内，适用于student表的单行数据的添加或删除create trigger tri_stuon studentafter insert,delete as if update(sno) update class set number=number+1 where clno = (select clno from inserted)else update class set number=number-1 where clno = (select clno from deleted)-验证触发器，添加数据insert into studentvalues (2222,tom,男,20,00311)-验证触发器，删除数据delete from studentwhere sno=2222-假设向student表添加或删除的多行数据都来自同一个班级create trigger tri_stu2on studentafter insert,deleteasif update(sno) update class set number=number+(select count(*) from inserted) where clno = (select clno from inserted)else update class set number=number-(select count(*) from inserted) where clno = (select clno from deleted)-适用于student表的多行数据的添加或删除（最靠谱解决方案）create trigger tri_stu2on studentafter insert,deleteasbegin declare sno char(7),clno char(5) if update(sno) begin declare mycursor cursor for select sno,clno from inserted -声明游标 open mycursor -打开游标 fetch next from mycursor into sno,clno -获取数据 while(fetch_status =0 ) /* 0操作成功，-1 FETCH 语句失败或此行不在结果集中，-2 被提取的行不存在 */ begin update class set number=number+1 where clno = clno fetch next from mycursor into sno,clno end close mycursor -关闭游标 deallocate mycursor -释放游标 end else begin declare mycursor cursor for select sno,clno from deleted open mycursor fetch next from mycursor into sno,clno while(fetch_status = 0) begin update class set number = number -1 where clno=clno fetch next from mycursor into sno,clno end close mycursor deallocate mycursor endend-为class表再建一更新触发器：当更新班长学号时，检查新输入的学号是否为同一班级的学生学号，若不是，给出适当的提示信息。create trigger stu_updateon classafter updateas if update(monitor) if ( select monitor from inserted ) not in ( select sno from student where clno = (select clno from deleted ) )begin print there is not the new monitor in the classrollback transactionend-验证触发器执行update classset monitor = 2001104where clno = 00312-创建商品表create table product( pno char(6) primary key, pname varchar(20) not null, price decimal(7,2) )-创建仓库表create table warehouse( whno char(3) primary key, whname varchar(20) not null, whaddress varchar(20) ) -创建库存商品表create table whproduct( whno char(3) references warehouse(whno) on delete no action on update cascade, pno char(6) references product(pno) on delete cascade on update cascade, number int )-设计触发器，当新增商品时，自动生成该商品在所有仓库的库存记录，库存数量为0create trigger tri_producton productafter insertasbegin declare pno char(3) select pno=pno from inserted insert into whproduct select whno,pno,0 from warehouse end-设计触发器，当新增