SQL server中使用触发器

触发器

• 是一特殊的存储过程,通过事件触发而执行的
• 可以实现比primary key,foregin key,check,unique,default更加复杂的数据完整性约束
• DML触发器:insert,delete,update
• DDL触发器:create,alter,drop

两个特殊表(deleted,inserted):系统自动管理,动态驻留内存中

 

1.DML触发器

为什么使用触发器呢?先来看一个示例代码

--视图:学号,姓名,课程号,课程名,成绩
create view view1
as
 select Student.Sno,Sname,Course.Cno,Cname,Grade
 from  Student,SC,Course
 where Student.Sno=SC.Sno and Course.Cno=SC.Cno

select * from view1


--插入数据,会提示错误,不可更新
insert into view1 values('2015005','005','5','python',100)

--结果会提示错误,因为修改会影响多个基表,因此可利用触发器,解决

--视图上只能创建instead of 触发器
create trigger tri_view1 on view1 instead of insert
as
		insert into Student(Sno,Sname) select Sno,Sname from inserted		
		insert into Course(Cno,Cname) select Cno,Cname from inserted
		insert into SC(Sno,Cno,Grade) select Sno,Cno,Grade from inserted


--测试触发器
insert into view1 values('2015005','005','5','python',100)


select * from Student
select * from Course
select * from SC

注:在视图上只能创建instead of触发器,不能创建after触发器;在基本表上可以创建instead of触发器和after触发器

 

2.DDL,DDL事件组

create...,alter...,drop...

create trigger tri_safe on database
--for create_table,drop_table,alter_table
for ddl_table_events --事件组
as
		print('数据表上的DDL触发器执行啦')
		print('您无法再该石油加油卡上创建表和创建表')
		rollback
--测试
create table test_table(
		no char(8),
		name char(20)
		)
select * from test_table

3.服务器触发器

create trigger tri_CheckLogin on all server 
 for  ddl_table_events
 as
		rollback
		print('不能再该数据库上创建,删除,修改登录账号')

create login aa with password='sa123'

--禁用
disable trigger all on all server

4.触发器运用示例

1)创建 DDL 触发器，禁止用户修改 BooksDB 数据库中的表；

实现代码:

创建 DDL 触发器，禁止用户修改 BooksDB 数据库中的表； 
create trigger tri_safe on Database
--for create_table,drop_table,alter_table
for ddl_table_events
as
	rollback
	print('DDL触发器执行啦')
	print('您无法在该数据库上修改表')
go
--测试
drop table Borrow

运行结果:

注:go--是批处理结束的语句.批处理:一条或多条SQL语句,一次性发送到SQL sever数据库服务器进行执行,SQL server数据库服务器讲批处理的语句编译成一个可执行单元,称为执行计划,当编译错误的时候不会执行;运行时错误;

2)测试 AFTER 触发器，理解 INSERTED 表和 DELETED 表的作用；

实现代码:

--测试 AFTER 触发器，理解 INSERTED 表和 DELETED 表的作用；
create trigger tri_testAfter on Borrow
for insert,delete
as
  select * from inserted
	select * from deleted
	print'Borrow表上的afetr触发器执行啦!'

--测试

insert into Borrow values('rd2018007','bk2018001',GETDATE(),GETDATE()+10,default)

select * from Borrow

delete Borrow where rdId='rd2018007'

运行结果:

思考题1: 请在SC表上创建一个触发器,当删除SC表中的选课记录时,要求平均成绩表自动更新

实现代码:

--思考:请在SC表上创建一个触发器,当删除SC表中的选课记录时,要求平均成绩表自动更新

--创建平均成绩表
select sno,avg(Grade) avgGrade into avgTable from SC group by sno

--创建触发器
create trigger tri_delSC on SC
for delete
as
	print'SC表上的delete触发器执行啦!'
	declare @sno char(9)
	set @sno=(select Sno from deleted)	
	update avgTable set avgGrade=(select avg(Grade) from SC  where sno=@sno group by sno) where sno=@sno
	print'平均成绩表更新啦'

--测试执行
  select * from avgTable
	select * from SC
	delete SC where sno='2017003' and Cno='2'
--查看数据
  select * from avgTable
	select * from SC

运行结果:

思考题2: 请在SC表上创建一个触发器,当更新SC表中的选课成绩时,要求平均成绩表自动更新

实现代码:

--思考:请在SC表上创建一个触发器,当更新SC表中的选课成绩时,要求平均成绩表自动更新


select sno,avg(Grade) avgGrade into avgTable from SC group by sno


create trigger tri_updateSC on SC
for update
as
	print'SC表上的update触发器执行啦!'
	declare @sno char(9)
	set @sno=(select Sno from inserted)	
	update avgTable set avgGrade=(select avg(Grade) from SC  where sno=@sno group by sno) where sno=@sno
	print'平均成绩表更新啦'

--测试执行

  select * from avgTable
	update SC set Grade=99 where sno='2017003' and Cno='1'
--查看数据
  select * from avgTable
	select * from SC

运行结果: