实验六：触发器设计实验

实验六：触发器设计实验
实验六：触发器设计实验一、实验目的：掌握触发器编写方法，能运用设计的触发器实现对数据表中数据完整性约束的控制。

二、实验内容：要求为Course表编写一个名为CourseTrigger插入触发器，当插入记录到Course表时，该触发器检查新插入记录的userid字段，如果插入这个字段的值不存在于Teacher表中，则从Course 表中删除这个新插入记录。

Teacher(userid char(20), name char(20),department char(20), age) Course(courseid int, userid char(20), name char(20), grade int) 说明：1．对表Teacher和Course之间不允许建立外键约束；2．本实验属于设计型实验，要求学生设计一个符合实验内容要求的触发器。

拓展训练： 1.在Student、Course和SC表中创建以下
触发器(1)创建一个after触发器，在Student表中删除某学生的记录时，删除其相应的选课记录(不允许使用外键级联删除约束)。

(2)创建一个触发器，不允许删除Course表中的任何数据。

2.利用实验五中的Work表，分别建立以下触发器：(1)为worker表建立触发器T1，当插入或更新表中的数据时，保证所操作的记录的sage值大于0。

(2)为worker表建立触发器T2，禁止删除编号为“0001”的职工。

(3)worker 表中的人员的编号是唯一且不可改变的，为worker表建立触发器T3实现更新中编号的不可改变性。

(4)为worker表建立触发器T4，要求插入记录的sage值必须比表中已记录的最大sage 值大。

三、实验学时2学时四、实验设备与环境Windows 2003平台+ SQL Server 2008系统--建立test 数据库create database test on (name=student, filename=‘d:\\db\\’, size=10MB, maxsize=50MB,
filegrowth=1MB ); use test; --要求为Course表编写一个名为CourseTrigger插入触发器，当插入记录到Course表时，该触发器检查--新插入记录的userid字段，如果插入这个字段的值不存在于Teacher表中，则从Course表中删除这个新插入记录。

--先建立Teacher、Course表create table Teacher(userid char(20), name char(20),department char(20), age int); create table Course(courseid int, userid char(20), name char(20), grade int); --建立CourseTrigger插入触发器create trigger CourseTrigger on Course for insert as declare @course_id char(20) set @course_id = (select userid from inserted) if not exists(select * from Teacher where userid = @course_id) begin delete from Course where userid=@course_id end; --验证插入这个字段的值不存在于Teacher表中：insert into
Course(courseid,userid,name,grade) values (4,’李勇’,’张三’,92); select * from Teacher; select * from Course; --验证插入这个字段的值存在于Teacher表中：insert into Teacher(userid,name,department,age) values (‘王五’,’张立’,’财务部’,29); insert into Course(courseid,userid,name,grade) values (5,’王五’,’刘晨’,85); select * from Course; --拓展训练：--1.在Student、Course1和SC表中创建以下触发器--建立student表create table student (Sno char(10) primary key, Sname char(10) unique, Ssex char(2), Sage int , Sdept varchar(10) ); --建立Course1表create table Course1 (Cno char(4) primary key,Cname char(20), Cpno char(4),Ccredit int, foreign key (Cpno) references Course1(Cno)); --建立SC表
create table SC(Sno char(10), Cno char(4),Grade int , --primary key(Sno,Cno), --foreign key(Sno)references student(Sno) --foreign key(Cno)references Course1(Cno)); --添加student 表中数据insert into student(Sno,Sname,Ssex,Sage,Sdept) Select ‘200215121’,’李勇’,’男’,20,’CS’ Union Select ‘200215122’,’刘晨’,’女’,19,’CS’ Union Select ‘200215123’,’王敏’,’女’,18,’MA’ Union Select ‘200215125’,’张立’,’男’,19,’IS’; --添加Course1表中数据insert into course1(Cno,Cname,Cpno,Ccredit) values (‘2’,’数学’,NULL,2); insert into course1(Cno,Cname,Cpno,Ccredit) values (‘6’,’数据处理’,NULL,2); insert into course1(Cno,Cname,Cpno,Ccredit) Select ‘4’,’操作系统’,’6’,3 Union Select ‘7’,’Pascal语言’,’6’,4 Union Select ‘5’,’
数据结构’,’7’,4 Union Select ‘1’,’数据库’,’5’,4 Union Select ‘3’,’信息系统’,’1’,4; --添加SC表中数据insert into SC(Sno,Cno,Grade) Select ‘200215121’,’1’,92 Union Select ‘200215121’,’2’,85 Union Select ‘200215121’,’3’,88 Union Select ‘200215122’,’2’,90 Union Select ‘200215122’,’3’,80;--(1)创建一个after触发器，在Student表中删除某学生的记录时，删除其相应的选课记录(不允许使用外键级联删除约束)。

--单个删除：--方法一：create trigger studelete on student for delete as declare @sno_id char(10) select @sno_id = sno from deleted if @sno_id is not null begin delete from sc where sno=@sno_id end --方法二：create trigger StudentTrigger on Student for delete as declare @sno_id char(10) set @sno_id=(select Sno from deleted )
if exists(select * from SC where Sno=@sno_id) begin delete from SC where Sno=@sno_id end --单个删除及全部删除：--方法一：create trigger StudentTrigger on Student for delete as if exists(select * from SC where Sno in (select Sno from deleted) ) begin delete from SC where Sno in(select Sno from deleted) end --方法二：
create trigger trdstudent on student for delete as delete SC from SC s,deleted d where = --验证: delete from Student where Sno=‘200215122’; select * from SC; select * from Student; --禁用触发器后再验证: alter table Student disable trigger StudentTrigger delete from Student where Sno=‘200215121’; select * from SC; select *
from Student; --(2)创建一个触发器，不允许删除Course1表中的任何数据。

create trigger Course1Trigger on Course1 for delete as if(select count(*) from deleted)>0begin print(‘你不能删除该表中的任何记录’) rollback transaction end --验证: delete from Course1 where Cno=‘2’; select * from Course1; --禁用触发器后再验证: alter table Course1 disable trigger Course1Trigger; delete from Course1 where Cno=‘3’; select * from Course1; --2.利用实验五中的Worker表，分别建立以下触发器：--创建表worker create table worker (number char(5), name char(8) constraint U1 unique, sex char(2), sage int constraint U2 check(sage constraint PK_workr primary key (number) ); --(1)为worker表建立触发器T1，当插入或更新表中的数据时，保证所操作的记录的
sage值大于0。

create trigger T1 on worker for insert,update AS If (select sage from inserted )print ‘年龄sage 必须大于0,插入或更新失败’ Rollback transaction End --验证: insert into worker(number,name,sex,sage,department) values (‘0001’,’张道’,’男’,-2,’财务部’) --禁用触发器后再验证: alter table worker disable trigger T1 insert into
worker(number,name,sex,sage,department) values (‘0001’,’张道’,’男’,-2,’财务部’) select * from worker; --(2)为worker表建立触发器T2，禁止删除编号为“0001”的职工。

create trigger T2 on worker for delete AS --declare @number_id char(5) If (select number from deleted )=‘0001’ begin print ‘不能删除编号0001的记录’ Rollback transaction End --验证: delete from worker where
number=‘0001’ --禁用触发器后再验证: alter table worker disable trigger T2 delete from worker where number=‘0001’ select * from worker; --(3)worker表中的人员的编号是唯一且不可改变的，为worker表建立触发器T3实现更新中编号的不可改变性。

create trigger T3 on worker for update AS if update(number) begin print ‘不能更改编号’ Rollback transaction End --验证: insert into worker(number,name,sex,sage,department) values (‘0001’,’张道’,’男’,-2,’财务部’); update worker set number=‘0002’ where number=‘0001’; --禁用触发器后再验证: alter table worker disable trigger T3 update worker set number=‘0002’ where number=‘0001’; select * from worker; --(4)为worker表建立触发器T4，要求插入记录的sage值必须比表中已记录的最大sage
================精选公文范文，管理类，工作总结类，工作计划类文档，欢迎阅读下载==============
值大。

Create trigger T4 on worker For insert AS IF(SELECT sage from inserted)print’插入记录的sage值必须比表中已记录的最大sage值大’ Rollback Transaction End --验证: insert into worker(number,name,sex,sage,department) values (‘0007’,’王旺’,’男’,-9,’人事部’); --禁用触发器后再验证: alter table worker disable trigger T4; insert into
worker(number,name,sex,sage,department) values (‘0007’,’王旺’,’男’,-9,’人事部’); select * from worker;
--------------------精选公文范文，管理类，工作总结类，工作计划类文档，感谢阅读下载--------------------- ~ 11 ~。