实验八、完整性和触发器设计
一、实验目的
通过实验掌握触发器的原理和基本设计方法。
二、实验原理
数据库的完整性是指数据的正确性和相容性，可以通过触发器来维护数据库的完整性。触发器是一种特殊的存储过程，在用户试图对指定的表执行指定的数据修改语句时自动执行。允许为任何给定的 INSERT、UPDATE 或 DELETE 语句创建多个触发器。
基本语法：
create trigger 触发器名
on 表名
for insert/update/delete
as
T-sql
三、实验内容和方法
1. 实体完整性约束及用户定义完整性：check约束
create table student1(sno numeric(6) constraint c1 check (sno between 200 and 500),
sname char(20) constraint c2 not null,
sage int constraint c3 check(sage>0 and sage<30),
ssex char(2) constraint c4 check(ssex in ('男','女')),
constraint s1 primary key (sno)
)
请设计测试用例测试约束是否生效,例如：
insert into student1 (sno,sage,ssex) values (150, 35, '无')
删除约束
alter table student1 drop constraint c1
增加约束
alter table student1 add constraint c1 check (sno between 100 and 500)
注意: c2和s1不是普通的约束，不能删除。
create table course1(cno numeric(6) constraint course_c1 check (cno between 1 and 10000),
cname char(10) constraint course_c2 check (cname like '计_') not null,
cpno numeric(6) constraint course_c3 check (cpno between 1 and 10000),
credit int constraint course_c4 check (credit between 1 and 5),
constraint course_c5 primary key (cno))
请设计测试用例测试约束是否生效。
create table teacher(
eno numeric(4) primary key,
ename char(10),
job char(8),
sal numeric(7,2),
deduct numeric(7,2),
deptno numeric(2),
constraint teacher_c1 check(sal + deduct>=3000)
)
请设计测试用例测试teacher_c1约束是否生效。
2. 参照完整性约束
create table sc1(sno numeric(6),cno numeric(6), grade float constraint sc_c check(grade>0 and grade<=100),
foreign key(sno)references student1(sno),
foreign key (cno) references course1(cno))
3. 缺省
create table sal_log1
(eno numeric(4),
sal numeric(7,2) constraint s_c2 default(3000),
Username char(10),
s_Date smalldatetime constraint sal_c1 default(getdate())
)
// s_Date 缺省值为自动增加时间
测试下列的语句
insert into sal_log1 (eno, sal, username) values (123, 3000,'user1')
并且用 select * from sal_log1查看缺省值是否生效。
4.触发器设计
（1）一个简单的触发器
在student表上设计一个触发器stu_trg1,当在student表上有发生插入、更新或者删除操作时，给出'HI，有人改动学生表！！'的消息。
第一步创建触发器
CREATE TRIGGER stu_trg1
ON student
FOR INSERT, UPDATE, DELETE
AS
print 'HI，有人改动学生表！！'
第二步 验证触发器
例如：update student set sno='95002' where sno='95008'
（2）插入触发器
在teacher表中设计一个插入触发器，当在teacher表插入一条记录，同时在sal_log表中插入一条记录！
注意在SQL server中， inserted和deleted是临时表分别代表有新纪录的表及原来的表！
create trigger insert_sal on teacher for insert
as begin
insert into sal_log (eno, sal) select eno,Sal from inserted
end
设计测试用例
insert into teacher values(12,'李立','职工', 2800,300.56,23)
insert into teacher values(13,'张三','教师', 3700,300.56,23)
检查结果是否符合。
（3）更新触发器
在teacher表中设计一个更新触发器，当在teacher表修改eno,sal值时，同时在sal_log1表中实现自动更新！
create trigger update_sal on teacher for update
as begin
update sal_log1 set eno=(select eno from inserted), sal=(select sal from inserted)
where sal_log.eno=(select eno from deleted) and sal_log.sal=(select sal from deleted)
end
特别注意inserted和deleted的用法区别
请设计测试用例验证更新触发器。
（4）删除触发器
在teacher表中设计一个删除触发器，当在teacher表删除一条纪录后，同时在sal_log1表中实现自动删除！
create trigger del_sal on teacher for delete
as begin
delete from sal_log1 where sal_log1.eno=(select eno from deleted)
print '相关记录已被删除'
end
请设计测试用例验证删除触发器。
（5）在表的定义中增加表的级联删除、更新（不需要再设计删除、更新触发器）
修改本实验中的SC1表的定义，注意增加了on delete cascade on update cascade
create table sc1(sno numeric(6),cno numeric(6), grade float constraint sc_c check(grade>0 and grade<=100),
foreign key(sno)references student1(sno) on delete cascade on update cascade,
foreign key (cno) references course1(cno) on delete cascade on update cascade)
设计测试用例验证级联删除、更新是否有效。
比较级联删除、更新与触发器的区别。