触发器之SQLServer

• 表结构

create table Grade
(
Id int unique not null,
Name varchar(10) not null
);
create table Student
(
Id int primary key identity(1,1),
Name varchar(10) not null,
GradeId int not null
);

• 假设有学生表和班级表，在添加学生的时候，该学生的班级编号如果在班级表中找不到，则自动添加班级信息，班级名称为"新班级"

drop trigger if exists tri_InsertPeople;
create trigger tri_InsertStudent on Student after insert
as
	begin
	declare @gradeId int;
	select @gradeId=GradeId from inserted;
	if not exists(select *from Grade where Id=@gradeId)
	begin
	insert into Grade(Id,Name) values(@gradeId,'新班级')
	end
	end
go
--演示
insert into Student(Name,GradeId) values('张三',2);

• 触发器实现，删除一个班级的时候将班级下所有学生全部删除

drop trigger tri_DeleteGrade;
create trigger tri_DeleteGrade on Grade after delete
as
	delete from Student where GradeId=(select Id from deleted);
go
--演示
delete from Grade where Id=2;

• 创建一个触发器，删除一个班级的时候判断该班级下是否有学生，有则不删除，没有则删除

create trigger tri_DeleteGrade1 on Grade instead of delete
as
begin
		declare @gradeId int;
		select @gradeId= Id from deleted;
		if not exists(select *from Student where GradeId=@gradeId)
		begin
				delete from Grade where Id=@gradeId;
		end
end
go
--演示
delete from Grade where Id=2;

• 修改一个班级编号之后，将该班级下所有学生的班级编号同步进行修改

create trigger tri_Update on Grade after update
as
	update Student set GradeId=(select Id from inserted)
	where GradeId=(select Id from deleted);
go

update Grade set Id=3 where Id=2;