SQL Server2008触发器练习题

触发器练习题

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)