sql触发器习题
??Ò???ʵÑéÈÎÎñÒ??º
1?? ????????Æ?
ÔÚÑ?ÉúÐÅÏ??ÜÀíϵÍ?ÖÐ??Ñ?ÉúÐÅÏ??í?üº?ÁÐ??Ñ?ºÅ??????ÐÕÃû??????ÐÔ?ð???????öÉúÄêÔÂ???????à??ºÅ?????à??ÐÅÏ??íÖÐ?üº?ÁÐ???à??ºÅ???????à??Ãû?Æ??????ÈËÊý?????Î?ÌÐÅÏ??í?üº?ÁÐ???Î?Ì?úºÅ???????Î?ÌÃû?Æ????Ñ?Éú?É???í?üº?ÁÐ??Ñ?ºÅ????ÁÐ???Î?Ì?úºÅ????ÁÐ???É??????ÒÑÓÃÔ?Êø??Ö??É??µÄ??Î?Ϊ0~100?Ö????ÓÃ??Â?ÖеÄ?Å????????
--1??ÔÚstudentÉÏ????INSERT????Æ?stu_insert??ÒªÇóÔÚstudent?íÖÐ?åÈë?ÇÂ?Ê?(ÒªÇóÃ??ÎÖ?ÄÜ?åÈëÒ?Ìõ?ÇÂ?)??Õâ?ö????Æ??????üÐÂclass?íÖеÄclass_nunÁÐ?????âÊÔ????Æ?stu_insert??
create trigger stu_insert
on student for insert
as
if @@rowcount>1
begin
RAISERROR('You cannot insert more than one student at a time.', 16, 1)
ROLLBACK TRAN
return
--×?Òâ?Ë??µÄreturnÓï?ä??ÄÜÊ?ÂÔ??ÒòΪÔÚ????Æ??Å??ÖеÄROLLBACK
TRANÓï?äÖ?ºó???æÔÚÓï?ä??ÄÇÃ????áÖ?ÐÐÕâÐ?Óï?ä???øΪÁËÆäºóµÄÓï?ä??Ö?ÐÐ???ØÐë?ÓÈëreturnÓï?ä
end
update class
set class_num=class_num+1
where class_id=(select class_id from inserted)
print 'class?íÖÐÊý?Ý?üÐÂ?É??'
go
--?âÊÔ1
insert into student
select '0601012','Àö','Å?','1986-07-11', '0601' union all
select '0601013','Ã?','Å?','1988-02-07', '0601'
--?âÊÔ2
insert into student values('0602011','ÎÄ','Å?','1986-09-21', '0602') --2??ÐÞ?ÄÌâ1ÖÐ????µÄINSERT????Æ?stu_insert??ÒªÇóÔÚstudent?íÖÐ?åÈë?ÇÂ?Ê?(ÔÊÐí?åÈë?àÌõ?ÇÂ?)??Õâ?ö????Æ??????üÐÂclass?íÖеÄclass_nunÁÐ?????âÊÔ????Æ?stu_insert?? alter trigger stu_insert
on student for insert
as
update class
set class_num=class_num+(select count(class_id) from inserted where class.class_id=inserted.class_id )
print 'class?íÖÐÊý?Ý?üÐÂ?É??'
go
--?âÊÔ1
insert into student
select '0601012','Àö','Å?','1986-07-11', '0601' union all select '0601013','Ã?','Å?','1988-02-07', '0601'
--?âÊÔ2
insert into student values('0602011','ÎÄ','Å?','1986-09-21', '0602')
--3??ÔÚstudentÉÏ????DELETE????Æ?stu_delete??ÒªÇóÔÚstudent?íÖÐÉ??ý?ÇÂ?Ê???Õâ?ö???
?Æ??????üÐÂclass?íÖеÄclass_nunÁÐ?????âÊÔ????Æ?stu_delete?? create trigger stu_delete
on student for delete
as
update class
set class_num=class_num-(select count(class_id) from deleted where
class.class_id=deleted.class_id )
go
--?âÊÔ1
delete from student where stu_id='0601001' --?âÊÔ2
delete from student
--4??Ϊ?ÀÖ?ÆäËûÈËÐÞ?Ä?É????ÔÚscoreÉÏ????UPDATE????Æ?sc_update??ÒªÇó??ÄÜ?üÐÂscore
?íÖеÄscoreÁÐ???âÊÔ????Æ?sc_update??
create trigger sc_update
on score for update
as
if update(score)
begin
print '??ÔÊÐíÐÞ?ÄscoreÁÐ'
rollback tran
end
go
--??ÊÔÐÞ?ÄscoreÁÐ
update score
set score=99
2???é??????Æ?Ïà?ØÐÅÏ?:Ê?ÓÃϵÍ??æ???ý?Ìsp_help??sp_helptext??sp_helptrigger?é????
??Æ?Ïà?ØÐÅÏ???
exec sp_help
exec sp_help sc_update
exec sp_helptext sc_update
exec sp_helptrigger student
exec sp_helptrigger student ,'insert'
--??Â?:
--????Êý?Ý?â,×???Êý?Ý
create database student_score
GO
--ÔÚÊý?Ý?âÖÐ????Èý?ö?íµÄ?á??
use student_score
GO
create table student
( stu_id char(8) primary key,
stu_name char(10),
stu_sex char(2),
stu_birthday smalldatetime,
class_id char(6)
)
go
create table class
( class_id char(6) primary key,
class_name varchar(30),
class_num int,
)
create table course
( course_id char(3) primary key,
course_name varchar(30),
)
go
create table score
( stu_id char(8),
course_id char(3),
score int check(score>=0 and score<=100)
primary key(stu_id,course_id)
)
go
--Íù?íÖÐ?åÈëÊý?Ý(student,course,score)
insert into student values('0601001','ÀîÓñ','Å?','1987-05-06', '0601')
insert into student values('0601002','Â?Ãô','Å?','1988-06-28', '0601')
insert into student values('0601003','ÀîÐ?Â?','Å?','1987-01-08', '0601')
insert into student values('0601004','Â??ó','ÄÐ','1988-04-21', '0601')
insert into student values('0601005','ÍõÄþ??','Å?','1986-05-29', '0601')
insert into student values('0601006','ÕÅÃ?Ã?','ÄÐ','1987-02-24', '0601')
insert into student values('0601007','ÁõÏþÁá','Å?','1988-12-21', '0601')
insert into student values('0601008','ÖÜÏþ','ÄÐ','1986-04-27', '0601')
insert into student values('0601009','Ò×?úÁº','ÄÐ','1985-11-26', '0601')
insert into student values('0601010','???ç','ÄÐ','1986-09-21', '0601')
insert into class values('0501','?ÆËã?ú?ì??Ó?ÓÃ', 40)
insert into class values('0502','ÍøÂç????', 43) insert into class values('0503','Í?ÐÎÍ?Ïñ', 48) insert into class values('0601','?ÉÊÓ??', 41) insert into class values('0602','Êý?Ý?â', 38) insert into class values('0603','ÍøÂç?ÜÀí', 45) insert into class values('0604','?àÃ?Ìå', 40) insert into class values('0701','?ÆËã?ú?ì??Ó?ÓÃ', 39) insert into class values('0702','WEBÓ?ÓÃ', 38) insert into class values('0703','ÍøÂç????', 40)
insert into course values('001','?ÆËã?úÓ?ÓÃ?ù??') insert into course values('002','?ØϵÊý?Ý?ù??') insert into course values('003','?ÌÐòÉè?Æ?ù??') insert into course values('004','Êý?Ý?á??') insert into course values('005','ÍøÒ?Éè?Æ') insert into course values('006','ÍøÕ?Éè?Æ') insert into course values('007','SQL Server 2000?ØϵÊý?Ý?â')
insert into course values('008','SQL Server 2000?ÌÐòÉè?Æ')
insert into course values('009','?ÆËã?úÍøÂç') insert into course values('010','Windows Server ÅäÖÃ')
insert into score values('0601001','001',78) insert into score values('0601002','001',88) insert into score values('0601003','001',65) insert into score values('0601004','001',76) insert into score values('0601005','001',56) insert into score values('0601006','001',87) insert into score values('0601007','001',67) insert into score values('0601008','001',95) insert into score values('0601009','001',98) insert into score values('0601010','001',45)
insert into score values('0601001','002',48) insert into score values('0601002','002',68) insert into score values('0601003','002',95) insert into score values('0601004','002',86) insert into score values('0601005','002',76) insert into score values('0601006','002',57) insert into score values('0601007','002',77) insert into score values('0601008','002',85) insert into score values('0601009','002',98) insert into score values('0601010','002',75)
insert into score values('0601001','003',88)
insert into score values('0601002','003',78) insert into score values('0601003','003',65) insert into score values('0601004','003',56) insert into score values('0601005','003',96) insert into score values('0601006','003',87) insert into score values('0601007','003',77) insert into score values('0601008','003',65) insert into score values('0601009','003',98) insert into score values('0601010','003',75)
insert into score values('0601001','004',74) insert into score values('0601002','004',68) insert into score values('0601003','004',95) insert into score values('0601004','004',86) insert into score values('0601005','004',76) insert into score values('0601006','004',67) insert into score values('0601007','004',77) insert into score values('0601008','004',85) insert into score values('0601009','004',98) insert into score values('0601010','004',75)
insert into score values('0601001','005',74) insert into score values('0601002','005',68) insert into score values('0601005','005',76) insert into score values('0601008','005',85) insert into score values('0601009','005',98) insert into score values('0601010','005',75)
insert into score values('0601002','006',88) insert into score values('0601003','006',95) insert into score values('0601006','006',77) insert into score values('0601008','006',85) insert into score values('0601010','006',55)
insert into score values('0601001','007',84) insert into score values('0601002','007',68) insert into score values('0601003','007',95)
insert into score values('0601004','008',86) insert into score values('0601005','008',76) insert into score values('0601006','008',67)
insert into score values('0601007','009',67) insert into score values('0601008','009',85)
insert into score values('0601009','010',98)
insert into score values('0601010','010',75)
本文档为【sql触发器习题】，请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑，
图片更改请在作品中右键图片并更换，文字修改请直接点击文字进行修改，也可以新增和删除文档中的内容。
该文档来自用户分享，如有侵权行为请发邮件ishare@vip.sina.com联系网站客服，我们会及时删除。
[版权声明] 本站所有资料为用户分享产生，若发现您的权利被侵害，请联系客服邮件isharekefu@iask.cn，我们尽快处理。
本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权，请谨慎使用。
网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传，仅限个人学习分享使用，禁止用于任何广告和商用目的。
浙公网安备 33021202002483