数据库完整性试验

一、定义一个教师表Teacher（Tno,TName,TSex,TAge,TSal,TJob,TDept）
要求：
    1、性别只能是‘男’或‘女’
    2、Tno为主码，名字不能为空，且不能重复。
    3、年龄在18在60岁之间
    4、教授工资最低为5000（触发器完成）
    5、Tdept为外码，参照DEPT表。当DEPT表删除元组时，相对应Teacher元组拒绝删除；修改时,相对应Teacher元组同时修改。
二、定义记录表Record（RNo,TNo,ROldSal,RNewSal,RUser,RTime）
要求：
    1、RNo从1开始，每次加1为。主码
    2、TNo为外码，参照Teacher表。
第一题和第二题参考：

 

use SC
go
/*-------------系表-----------------------------*/
create table Dept
(TDept char(10) primary key);
go

/*-------------教师表-----------------------------*/
create table Teacher
(TNo char(10) primary key,
TName char(10) not null,
TSex char(2)check(TSex in('男','女')),
TAge smallint check(TAge>=18 and TAge<=60),
TSal numeric(7,2),
TJob varchar(10),
TDept char(10),
foreign key (TDept) references Dept(TDept)on delete no action   on update cascade);  
go   

 

/*-----教授工资不低于5000触发器-----------------*/ 
create trigger Teacher_JiaoShou_MINSal 
on  Teacher 

for Insert,Update as 

set Nocount off
/*---------定义变量----------------------*/
declare 
 @TNo char(10),
 @NewSal numeric(7,2),
 @Tjob char(10)
begin
 /*---------获得变量值----------------------*/
 select @TNo=TNo,
 @NewSal=TSal,
 @Tjob=Tjob 
 from inserted 

 /*---------判断是否是教授，且工资小于5000----------------------*/
 if (((@NewSal-5000)<0) and (@Tjob='教授'))
  begin
   /*---------修改该员工（教授Tno）表----------------------*/
   update Teacher 
   set Tsal=5000
   where Tno=@Tno
  end
end

/*-------------记录表-----------------------------*/
create table Record
(RNo  int  identity (1,1) primary key,
TNo char(10) references Teacher(TNo),
ROldSal numeric(7,2),
RNewSal numeric(7,2),RUser char(10), RTime Datetime);
go    

三、定义触发器
    1、触发器Insert_Teacher,当插入Teacher一行元组时，记录教师编号TNo，等数据信息，插入到表Record中。ROldSal为0。

参考：

 

/*-----插入Teacher触发器-----------------*/ 
create trigger Insert_Teacher 
on  Teacher 

for Insert as 

set Nocount off
/*---------定义变量----------------------*/
declare 
 @TNo char(10),
 @RNewSal numeric(7,2)
begin
 /*---------获得变量值----------------------*/
 select @TNo=TNo,
 @RNewSal=TSal 
 from inserted 
 /*---------插入要记录的数据到Record表----------------------*/
 insert into Record(TNo,ROldSal,RNewSal,RUser,RTime) values(@TNo,0,@RNewSal,current_user,current_timestamp)
end

测试数据：

insert into dept
values('信息工程系')
insert into teacher
values ('2008001','wang','男',34,2000,'讲师','信息工程系')
select * from teacher

 

    2、触发器Update_Teacher,当修改Teacher数据时，若工资发生变动，记录变动情况到Record表。

参考：

 

 

/*-----更新Teacher触发器-----------------*/ 
create trigger Update_Teacher 
on  Teacher 

for update as 

set Nocount off
/*---------定义变量----------------------*/
declare 
 @TNo char(10),
 @ROldSal numeric(7,2),
 @RNewSal numeric(7,2)

begin
 /*---------获得变量值----------------------*/
 select @TNo=TNo,
 @ROldSal=TSal
 from deleted 

 select @TNo=TNo,
 @RNewSal=TSal 
 from inserted 
 /*---------插入要记录的数据到Record表----------------------*/
 insert into Record(TNo,ROldSal,RNewSal,RUser,RTime) values(@TNo,@ROldSal,@RNewSal,current_user,current_timestamp)
end

测试：

update teacher
set tsal=3000
where tno='2008001'

select * from record

    3、（课下完成）触发器Delete_Teacher,当删除Teacher数据时，记录删除情况到Delete_teacher表。记录教师所有信息和用户及删除时间。

参考：

    4、完成以上触发器的测试。

 

 

说明：

1.能够触发Insert_Teacher触发器的，有对Teacher表进行Insert操作和Update操作。

2.能够触发Delete_Teacher触发器的，有对Teacher表进行Delete操作和Update操作。

3.能够触发Update_Teacher触发器的，只有有对Teacher表进行Update操作