【无标题】数据库触发器练习题

create database test_5;
use test_5
--1-----------------------------------------------
/*
研究人员（人员编号，姓名，年龄，职称）
项目（项目编号，名称，负责人编号，类别）
参与（项目编号，人员编号，工作时间）/****一个研究人员可以参加多个项目，一个项目有多个研究人员参加，工作时间给出某研究人员参加某项目的月数****/
*/
create table 研究人员(
     人员编号 int, 
     姓名 char(8), 
     年龄 smallint, 
     职称 char(8) 
);

create table 项目 (
    项目编号 int, 
    名称 char(20), 
    负责人编号 int, 
    类别 char(8)
);

create table 参与 (
    项目编号 int, 
    人员编号 int, 
    工作时间 smallint /*月数*/
)

--1)通过修改表方式定义三个关系中的主码、外码、参照完整性；
alter table 研究人员 alter column 人员编号 int not null
alter table 研究人员 add constraint 研究人员pk_人员编号 primary key(人员编号)

alter table 项目 alter column 项目编号 int not null
alter table 项目 add constraint 项目pk_项目编号 primary key(项目编号)
alter table 项目 add constraint 项目fk_负责人编号 foreign key(负责人编号) references 研究人员(人员编号)

alter table 参与 alter column 项目编号 int not null
alter table 参与 alter column 人员编号 int not null
alter table 参与 add constraint 参与pk_项目编号_人员编号 primary key(项目编号,人员编号)
alter table 参与 add constraint 参与fk_项目编号 foreign key(项目编号) references 项目(项目编号)
alter table 参与 add constraint 参与fk_人员编号 foreign key(人员编号) references 研究人员(人员编号)

--2)创建约束，如果研究人员职称为教授，则年龄不小于35；
alter table 研究人员 add constraint 研究人员年龄 check(年龄 <= 35 or (职称 ='讲师' or 职称 = '副教授'))

--3)创建约束，要求每个研究人员职称只能是“讲师”、“副教授”或“教授”；
alter table 研究人员 add constraint 研究人员职称 check(职称 in('讲师','副教授','教授'))

--4)使用断言实现，要求一个研究人员参加各种项目的总工作时间不能超过12个月；
IF (OBJECT_ID('插入参与项目触发器', 'TR') IS NOT NULL)
    DROP TRIGGER 插入参与项目触发器
GO
create trigger 插入参与项目触发器
on 参与
after insert
AS
begin
    declare @项目编号 int,@人员编号 int,@new工作时间 smallint,@old工作时间 smallint,@总工作时间 smallint;
    select @项目编号 = 项目编号 from inserted
    select @人员编号 = 人员编号 from inserted
    select @new工作时间 = 工作时间 from inserted
    select @old工作时间 = 工作时间 from deleted

    select @总工作时间 = sum(工作时间) from 参与 where 人员编号=@人员编号

    if @总工作时间+@new工作时间 > 12
    begin
        delete 参与 where 项目编号=@项目编号 AND 人员编号=@人员编号 AND 工作时间=@new工作时间;
        print '插入失败，一个研究人员参加各种项目总工作时间不能超过12个月'
    end
end

--5)使用断言实现每个项目至少有5位研究人员；
IF (OBJECT_ID('项目总人数触发器', 'TR') IS NOT NULL)
    DROP TRIGGER 项目总人数触发器
GO
create trigger 项目总人数触发器
on 参与
after insert,delete
AS
begin
    declare @项目编号 int,@项目总人数 smallint;
    select @项目编号 = 项目编号 from inserted
    select @项目总人数 = count(*) from 参与 where 项目编号 = @项目编号

    if @项目总人数 < 5
    begin
        print '项目人数不足5人，插入失败'
        delete 参与 where 项目编号=@项目编号
    end
end

--6)使用断言实现每个项目至少有5位研究人员；
IF (OBJECT_ID('参与项目限制触发器', 'TR') IS NOT NULL)
    DROP TRIGGER 参与项目限制触发器
GO
create trigger 参与项目限制触发器
on 参与
after insert
AS
begin
    declare @项目编号 int,@人员编号 int,@参与总项目数 smallint;
    select @参与总项目数 = count(*) from 参与 where 人员编号 = @人员编号

    if @参与总项目数 > 3
    begin
        print '每个研究人员参加项目不能超过3个，插入失败'
        delete 参与 where 项目编号=@项目编号 AND 人员编号=@人员编号
    end
end

--2-----------------------------------------------
/*
对于以下关系模式，创建触发器，完成相应功能(要求触发器名称以TR开头，以自己姓名首字母为结尾，如TR_TCH_INS_WHY):
Teacher(Tno,Tname,Tage,Tsex)、Department(Dno,Dname,Tno)、Work(Tno,Dno,Year,Salary)
*/
create table Teacher (
    Tno int primary key,
    Tname char(10),
    Tage smallint,
    Tsex char(2))
create table Department ( --部门
    Dno int primary key,
    Dname char(50),
    Tno int,
    foreign key(Tno) references Teacher(Tno))
create table Work (
    Tno int primary key,
    Dno int,
    Year int,
    Salary int,--工资
    foreign key(Tno) references Teacher(Tno),
    foreign key(Dno) references Department(Dno))

--1)在插入新教师信息时，同时自动将此教师信息插入Work关系中，不确定的属性赋值NULL；
IF (OBJECT_ID('插入新教师', 'TR') IS NOT NULL)
    DROP TRIGGER 插入新教师触发器
GO
create trigger 插入新教师触发器
on Teacher
after insert
AS
begin
    declare @Tno int;
    select @Tno = Tno from inserted
    insert into Work values(@Tno,null,null,null)
end

--2)在更新教师年龄时，如果新年龄比旧年龄低则用旧年龄代替。
IF (OBJECT_ID('更新教师年龄', 'TR') IS NOT NULL)
    DROP TRIGGER 更新教师年龄触发器
GO
create trigger 更新教师年龄触发器
on Teacher
after update
AS
begin
    declare @Tno int,@Tname char(10),@new_Tage smallint,@old_Tage smallint,@Tsex char(2);
    select @Tno = Tno from inserted
    select @Tname = Tname from inserted
    select @new_Tage = Tage from inserted
    select @old_Tage = Tage from deleted
    select @Tsex = Tsex from deleted
    update Teacher set Tage = @old_Tage where @Tno = Tno AND @Tname = Tname AND @new_Tage<@old_Tage
end