2018年11月16日SQL Server实验内容（触发器实验）

--注意：先把studentmanager数据库中的所有表用select  into命令复制一份，

--然后用复制后新表完成下面的实验，同时，对每个触发器都要进行验证。

select *into department2 from department

select *into course2 from course

select *into class2 from class

select *into teacher2 from teacher

select *into t_c2 from t_c

select *into s_c2 from s_c

select *into student2 from student

--1、创建触发器tr_s1，在录入学生信息时同时显示表中的所有记录，并执行测试语句。

create trigger tr_s1

on student2

for insert

as

select *from student2

print'信息已录入学生表！from_tr_s1'

go

insert into student2(s_id,s_name) values('2016030303','aa')

--2、创建触发器tr_s2，

--在录入学生信息时同时将此学生的学号及大学英语课程号录入到成绩表s_c中（大学英语是公共必修课），

--同时显示录入的记录信息。

create trigger tr_s2

on student2

for insert

as

declare @v_sid char(10)

declare @v_cid char(10)

select @v_sid=(select s_id from inserted)

select @v_cid=

(

	select distinct c.course_id from s_c2,course2 c

	where s_c2.course_id=c.course_id and course_name='大学英语'

)

insert into s_c2(s_id,course2.course_id) values(@v_sid,@v_cid)

print'信息已录入成绩表！from_tr_s2'

select *from s_c2 where s_id=@v_sid

go

insert into student2(s_id,s_name) values('2016030305','bc')

--3、创建触发器tr_s3，在删除一个毕业生的信息的同时删除这个学生的成绩记录。

--如果没有此学生则给出提示信息。

create trigger tr_s3

on student2

for delete

as

declare @v_sid char(10)

select @v_sid=(select s_id from deleted)

if not exists(select *from s_c2 where s_id=@v_sid)

print '没有此学生from_tr_s3'

else

delete from s_c2 where s_id=@v_sid

go

delete from student2 where s_id='2016030305'

select *from s_c2

--4、 创建触发器tr_s4，当更新某个学生的学号时更新这个学生的选课信息。

create trigger tr_s4

on student2

for update

as

declare @v_sid char(10)

select @v_sid=(select s_id from inserted)

declare @v_dsid char(10)

select @v_dsid=(select s_id from deleted)

if UPDATE(s_id)

begin

update s_c2 set s_id=@v_sid where s_id=@v_dsid

print '更新成功！from_tr_s4'

select * from s_c2

end

go

insert into student2(s_id,s_name) values('2016030310','dr')

update student2 set s_id='2016030311' where s_id='2016030310'

select *from s_c2

select *from student2 where s_id='2016030310'

--5、创建触发器tr_s5，在向学生表插入、更新记录时检测出生日期与入学日期的合法性，

--即出生日期应早于入学日期，若不合法给出提示，并撤销此操作。

create trigger tr_s5

on student2

for insert,update

as

declare @v_S_Born datetime

select @v_S_Born=(select s_borndate from inserted)

declare @v_S_Enroll datetime

select @v_S_Enroll=(select s_enrolldate from inserted)

if ( @v_S_Born)>=(@v_S_Enroll)

begin

raiserror ('error!出生日期应早于入学日期from_tr_s5',16,1)

rollback transaction

end

go 

insert into student2(s_id,s_name,s_borndate,s_enrolldate) values('2016030313','dcc',2016-01-01,1998-10-16)

--error!出生日期应早于入学日期

--消息 3609，级别 16，状态 1，第 1 行

--事务在触发器中结束。批处理已中止。

select * from student2

--6、创建触发器ts_s6，用来控制不能更新“姓名”列。

create trigger ts_s6

on student2

for update

as

if UPDATE(s_name)

begin

raiserror('error!姓名不能更新！from_tr_s6',16,1)

rollback transaction

end

go

update student2 set s_name='ccc' where s_name='dr'

--姓名不能更新

--消息 3609，级别 16，状态 1，第 1 行

--事务在触发器中结束。批处理已中止。

select*from student2

--7、分别使用管理平台和SQL语句删除、禁用、启用触发器

--（1）删除触发器tr_21  drop trigger 触发器名

drop trigger tr_21

--（2）禁用触发器tr_s1    alter table  表名  disable trigger 触发器名

alter table student2 disable trigger tr_s1

--（3）启用触发器tr_s1    alter table  表名  enable trigger 触发器名

alter table student2 enable trigger tr_s1

附录：实验过程中用到的知识：

1、使用CREATE TRIGGER语句定义触发器，ALTER TRIGGER语句修改触发器，DROP TRIGGER语句删除触发器。

2、如果触发语句对应的表上有完整性约束，这些完整性约束必须不违背时，相应的触发语句才能执行，然后才能触发对应的触发器。

3、SQL SERVER为每个触发器都创建了两个临时表INSERTED表和DELETED表，这两个表的逻辑结构与被触发器作用的表一样，用户可以读取这两个表的内容，但不能对它们进行修改，触发器执行完后，这两个表也会自动删除。当执行INSERT时，INSERTED表中保存要向表中插入的所有行；当执行DELETE时，DELETED表中保存要从表中删除的所有行；当执行UPDATE时，修改前的行保存在DELETED表中，修改后的行保存在INSERTED行中。

巴特西

2018年11月16日SQL Server实验内容（触发器实验）

最新文章

热门文章