笔记一：触发器部分

1
2
3
4
5
6
7

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

--创建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

1
2
3
4
5
6
7
8

--测试触发器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;

1
2
3
4
5
6
7
8
9

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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

1
2
3
4
5
6
7

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

1
2
3
4
5
6
7

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

1
2
3

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

1
2
3
4
5
6
7
8

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

1
2
3
4
5

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