文档介绍:数据库技专题训练1(2014年春)
成绩:
评语:
数据库专题训练……触发器
实验报告
系别:计算机科学与技术
班级:计11-3班
姓名:
学号:
指导教师签字: 日期:
Owner Type
Crearted_datetime
J_
tri_lnsert_S :
dbo trigger
2D14-D4-15K:3€:
Tart
1
-Batch submitt&d through debugger:实险1 .mql|25D...
2
crests trigg&r tri_lnsert_S on £ after insert as if exist...
irigger_narre
trigger_owner
isupdate isdelete isinsert isafter isinsteadcf trigger_5chema
1
tri_ln3ert_S
:dbo
D D 1 1 & dbo
4)禁用tri_Update_SC触发器,并演示禁用该触发器后Update的执行情况。
use SXCJ
g°
alter table SC disable trigger tri_Update_SC
update SC
set Score='95'
where Sno='S1' and Cno='C3'
print'修改成功!’
select *
from SC
Go
显示如下:
口J hies sage 5
(1 row ( s ) afrected.) 修改成功!
J Resuits _j
在SC表上创建一个instead of类型的触发器,触发器名称为:
tri__Insert_SC。实现:当向表SC插入记录时检查分数的合理性,分数必须在
0到100之间,如果不合理就拒绝插入,并给出信息提示,
use SXCJ
go
if exists(select name from sysobjects
where name 'tri__InseCrt_SC' and type='TR')
begin
drop trigger tri__InseCrt_SC
end
else
begin
create trigger tri__InseCrt_SC on SC
instead of insert
as
if exists(select * from inserted
where Score>=0 and Score<=100)
begin
insert into SC
select * from inserted
print'插入成功!’
end
else
begin
rollback transaction
print '分数必须在到之间,插入数据失败!’
end
end
g。
insert into SC
values('S3','C3',110)
go
显示如下:
.J] Messages
分数必须在口到[品之间,插入数据失畋I
Msg- 3^09r Level lZr St^ts lr Line 1 The ±zansac"