笔记一：触发器部分

我们以 INSERT 触发器的创建为例，讲解触发器的创建和使用。首先创建测试数据表：

--创建学生表
create table student(
    stu_id int identity(1,1) primary key,
    stu_name varchar(10),
    stu_gender char(2),
    stu_age int
)

为 student 表创建 INSERT 触发器：

--创建insert触发器
create trigger trig_insert
on student
after insert
as
begin
    if object_id(N'student_sum',N'U') is null--判断student_sum表是否存在
        create table student_sum(stuCount int default(0));--创建存储学生人数的student_sum表
    declare @stuNumber int;
    select @stuNumber = count(*)from student;
    if not exists (select * from student_sum)--判断表中是否有记录
        insert into student_sum values(0);
    update student_sum set stuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中
end

--测试触发器trig_insert-->功能是向student插入数据的同时级联插入到student_sum表中，更新stuCount
--因为是后触发器，所以先插入数据后，才触发触发器trig_insert;
insert into student(stu_name,stu_gender,stu_age)values('吕布','男',30);
select stuCount 学生总人数 from student_sum;
insert into student(stu_name,stu_gender,stu_age)values('貂蝉','女',30);
select stuCount 学生总人数 from student_sum;
insert into student(stu_name,stu_gender,stu_age)values('曹阿瞒','男',40);
select stuCount 学生总人数 from student_sum;

另外，因为定义学生总数表 student_sum ，是向 student 表中插入数据后，才计算的学生总数。所以，学生总数表应该禁止用户，向其中插入数据。

--创建insert_forbidden,禁止用户向student_sum表中插入数据
create trigger insert_forbidden
on student_sum
after insert
as
begin
    RAISERROR('禁止直接向该表中插入记录，操作被禁止',1,1)--raiserror 是用于抛出一个错误
rollback transaction
end

实验部分

向 score 表建立一个插入触发器。保证向 score 表中插入的学生信息的学号，必须在 student 表中存在

create trigger trigger_insert_score
on score
after insert
as
Begin
    if not exists (select * from student where sno in(select sno from inserted))
    Begin
        rollback transaction
        Begin transaction
    End
End

insert into score values('1001','2001','89.5')
go
insert into score values('1002','2001','95')
go
insert into score values('1011','2001','88')
go
select * from score

向 student 表插入删除触发器，实现 student 表和 score 表的级联删除；

create trigger trigger_delete_student
on student
for delete
as
Begin
delete score where sno in (select sno from deleted)
End

delete from student where sno='1001'
go
select * from student

向 score 表建立触发器，使 grade 列不能手工修改

create trigger trigger_protect_grade
on score
for update
as
Begin
if update(grade)
    raiserror('cannot modify the grade',16,1)
End

delete from score where sno='1001'
go
select * from score
go
select * from student