使用SQL语句创建触发器的实例

扎眼的阳光

10 月前

Table of Contents

前言

一、触发器的介绍

1.1 触发器的概念以及定义:

触发器是一种特殊类型的存储过程，它不同于我们前面介绍过的存储过程。存储过程可以通过语句直接调用,而触发器主要是通过事件进行触发而被执行的.
例如当对某一表进行诸如UPDATE(修改)、INSERT(插入)、DELETE(删除)这些操作时，SQL Server 就会自动执行触发器所定义的SQL语句，从而确保对数据之间的相互关系,实时更新.

1.2 、触发器的作用

触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有其它许多不同的功能：

①、复杂的约束条件
触发器能够实现比CHECK 语句更为复杂的约束。

②、保证数据的安全

触发器因为触发器是在对数据库进行相应的操作而自动被触发的SQL语句可以通过数据库内的操作从而不允许数据库中未经许可的指定更新和变化。

③．级联式

触发器可以根据数据库内的操作，并自动地级联影响整个数据库的各项内容。例如：对A表进行操作时,导致A表上的触发器被触发,A中的触发器中包含有对B表的数据操作（UPDATE(修改)、INSERT(插入)、DELETE(删除)），而该操作又导致B表上触发器被触发。

④．调用存储过程

为了响应数据库更新，触发器可以调用一个或多个存储过程.

但是,总体而言，触发器性能通常比较低。

二、触发器的种类

SQL Server 中一般支持以下两种类型的触发器:

AFTER 触发器

AFTER 触发器要求只有执行某一操作(INSERT、UPDATE、DELETE)之后，触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器。
2. INSTEAD OF 触发器。

INSTEAD OF 触发器表示并不执行其所定义的操作(INSERT、UPDATE、DELETE)，而仅是执行触发器本身。既可在表上定义INSTEAD OF 触发器，也可以在视图上定义INSTEAD OF 触发器，但对同一操作只能定义一个INSTEAD OF 触发器。

三、使用SQL语句创建触发器实例

1.创建after融发器

(1）创建一个在插入时触发的触发器sc_insert,当向sc表插入数据时，须确保插入的学号已在student表中存在，并且还须确保插入的课程号在Course表中存在﹔若不存在，则给出相应的提示信息，并取消插入操作，提示信息要求指明插入信息是学号不满足条件还是课程号不满足条件（注:Student表与sc表的外键约束要先取消)。

语句实现:

create trigger sc_insert
on sc
after insert
as
if not exists (select * from student,inserted
			where student.sno=inserted.sno)
	begin
		print '插入信息的学号不在学生表中! '
		if not exists (select * from course,inserted where
		course.cno=inserted. cno)
		print '插入信息的课程号不在课程表中!'
		rollback
	end
  else
		begin
			if not exists (select * from course,inserted where
		Course.cno=inserted.cno)
			begin
			print '插入信息的课程号不在课程表中! '
			rollback
		end
	end

执行:

insert into sc
values ( '20110112','001','78')

删除外键约束;

alter table sc
drop constraint FK_sc__sno__332C9D34

(2）为Course表创建一个触发器Course_del，当删除了Course表中的一条课程信息时，同时将表sc表中相应的学生选课记录删除掉。

create trigger course_del
on course
after delete
as
if exists(select * from sc, deleted
where sc.cno=deleted.cno)
begin
delete from sc
where sc.cno in (select cno from deleted)
end
delete from Course
where Cno='003'

select * from sc

(3）在Course表中添加一个平均成绩avg_Grade字段（记录每门课程的平均成绩)，创建一个触发器Grade_modify，当sc表中的某学生的成绩发生变化时，则Course表中的平均成绩也能及时相应的发生改变。

向Course表中添加一个平均成绩avg_Grade字段

alter table Course
add avg_Grade smallint

向avg_Grade中添加数据

update course
set avg_Grade= (select AVG(Grade) from sc
where sc.Cno=Course.Cno)

查看Course表:

select *from Course

创建Grade_modify触发器

create trigger Grade_modify
on sc
after update
as
if update(grade)
begin
update course
set avg_grade=(select avg (grade)
from sc where course.cno=sc.cno
group by cno)
end
update sc
set Grade='90 '
where sno='20050001' and cno='001'

2.创建instead of触发器

(1）创建一视图Student_view,包含学号、姓名、课程号、课程名、成绩等属性，在Student_view上创建一个触发器Grade_moidfy,当对Student_view中的学生的成绩进行修改时，实际修改的是sc中的相应记录。

创建视图:

create view student_view
as
select s.Sno,Sname , c.Cno , Cname , Grade
from student s , course c, sc
where s.Sno=sc.sno and c.Cno=sc.cno

创建触发器:

create trigger Grade_moidfy 
on student_view
instead of update
as
if UPDATE (Grade)
begin
update sc
set Grade= (select Grade from inserted) where
Sno= (select sno from inserted) and 
Cno= (select Cno from inserted)
End
update student_viewset Grade=40
where Sno='20110001'and Cno='002'

测试修改数据:

select *
from student_view

(2）在sc表中插入一个getcredit字段(记录某学生，所选课程所获学分的情况)，创建一个触发器ins_credit，当更改（注:含插入时）sc表中的学生成绩时，如果新成绩大于等于60分，则该生可获得这门课的学分，且该学分须与Course表中的值一致﹔如果新成绩小于60分，则该生未能获得学分，修改值为0。

添加新字段getcredit :

alter table sc
add getcredit smallint

创建触发器:

create trigger sc_up
on sc
after insert,update
as
declare @xf int,@kch char(3),@xh char(8),@fs int
select @fs=grade,@kch=cno,@xh=sno from inserted
if @fs>=60
update sc set @xf=(select credit from course where
sc.Cno=course.cno) where sno=@xh and cno=@kch
else
update sc set @xf=0 where sno=@xh and cno=@kch
修改数据:
update sc
set Grade='90'
where Sno='20050001' and cno='001'

以上就是对触发器的基本认识了,希望对大家有所帮助.

到此这篇关于使用SQL语句创建触发器的实例的文章就介绍到这了,更多相关SQL创建触发器内容请搜索aitechtogether.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持aitechtogether.com！

前言