SQL Server 数据库的维护(二)__触发器

时间:2022-11-25 21:51:35

--维护数据库--



--触发器--

--概述

  触发器是一种特殊类型的存储过程用来强制执行业务规则。在调用执上,触发器不能像存储过程那样可以由用户通过T-SQL语句直接调用,而是需要有数据库所发生的insert、update、delete事件的执行来激发触发器的自动执行。因此在创建和使用触发器时,需要考虑好触发事件的执行和被触发事件后,再设计和创建触发器,已完成用户需求。
触发器可以向约束一样,在数据表和视图中的数据发生改变时强制执行业务规则。在某种角度上,触发器要优于约束,因为触发器可以包含使用T-SQL语言编写的复杂代码,可以涉及其他数据表中的数据。约束能够完成的功能触发器都能可以做到,但它所给出的解决方案并不一定总是最好的。因此,约束和触发器在不同情况下各有优势。
与存储过程一样,创建触发器的语句必须存在于同一个批处理中
--分类:按照触发事件的语言分类,可以将触发器分成DML触发器DDL触发器
1)DML触发器:是指可以被对数据表的insert、update和delete操作所触发的触发器
2)DDL触发器:是指可以被数据库对象的create、alter、drop操作所触发的c触发器
  两种触发器的创建、修改和删除方法相似。
  --DML触发器(详细介绍):
注:DML触发器按照触发器的触发和执行事件的不同,可分为after类型触发器instead of类型触发器
  ♦after类型触发器(后触发触发器):如果把被触发器封装的T-SQl语句所代表的事件称为“被触发事件”,把触发触发器执行的insert、update或者delete语句所代表的事件称为“触发事件”。after类型触发器即是时指“触发事件”成功执行完毕后,“被触发事件”执行的触发器。
after类型触发器只能创建在数据表上,而不能创建在视图上。一张数据表可以创建多个因同一触发操作而生成的触发器,但同一触发器操作所激发的触发器的执行顺序随机,可以通过对触发器禁用和恢复的方法设置执行那个触发器。
  ♦instead of类型触发器(替换触发器):简单地说,instead of类型的触发器当“触发事件”发生时,系统并不执行“触发事件”的具体操作(比如insert、update或者delete数据),而是直接执行“被触发事件”。
instead of类型触发器可以定义在表上和视图上。对于每个触发操作(insert、update或delete)只能定义一个instead of类型触发器。

 

--inserted表和deleted表
注:inserted表和deleted表是系统为每个触发器准备的临时表,存放于内存中。当触发器被触发执行时,inserted表和deleted表将记录触发器执行过程中设计的每条记录信息,以方便用户使用和查询。inserted表和deleted表中的记录只能查看,不能修改,当触发器执行完毕后,与之相关的临时表也随之删除
没有“updated”表,update作为修改操作,既相当与删除数据再添加数据,因此,当触发操作作为update命令时,即生成deleted表又生成inserted表。
  ♦inserted表
    inserted表用来存放当触发操作为insert命令所产生的数据信息。该表的结构与insert命令所作用的表结构相同,表中信息作为副本存储,且只存储新发生的记录信息。
  ♦deleted表
    deleted表用来存放当触发操作为delete命令所产生的数据信息。该表的结构与delete命令所作用的表结构相同,表中信息作为副本存储,且只存储新发生的记录信息。
--触发器管理--
--创建触发器(create trigger)--
注:在SQL Server中,触发器可以是使用“管理器”和T-SQL语言两种方式创建。执行触发器其实即是执行了定义触发器时的触发命令(insert、update和delete)
create trigger 触发器名 --create trigger命令表示创建触发器,该命令要求是批处理中的第一句话。
on 数据表名 --“on 数据表名”指“触发事件”发生的数据表。
[with encryption] --with encryption语句可以对创建不同类型的触发器。
after | instead of [insert] [, update] [,delete] --after和instead of是可选项,用来创建不同类型的触发器。insert、update、delete是执行操作,可以单选也可以多选,没有先后顺序。
as --as后的T-SQl语句是“被触发事件”。
T-SQl语句

SQL Server 数据库的维护(二)__触发器SQL Server 数据库的维护(二)__触发器
  例1:(after类型触发器)(创建名为“t_客户信息表_电话”的触发器,当“商品管理数据库”中“客户信息表”的客户编号为20130001的客户联系电话修改为13600003333操作成功执行后,在结果打印一条“记录已修改!”的提示信息。)

--创建触发器
use 商品管理数据库
go
create trigger t_客户信息表_电话
on 客户信息表
after
update --after类型触发器
as
print '记录已修改!'
select*from 客户信息表

--执行“触发事件”激发触发器执行
use 商品管理数据库
go
update 客户信息表
set 联系电话='13600003333' where 客户编号='20130001'
go



  例2:(instead of类型触发器)(创建名为“t_客户信息表_电话no”的触发器,当“商品管理数据库”中“客户信息表”的客户编号为20130001的客户联系电话修改为13600004444时,不执行修改操作,并在结果打印一条“记录未修改!”的提示信息。)

--创建触发器
use 商品管理数据库
go
create trigger t_客户信息表_电话no
on 客户信息表
instead
of update
as
print '记录未修改!'
select*from 客户信息表

--执行“触发事件”激发触发器执行
use 商品管理数据库
go
update 客户信息表
set 联系电话='13600004444'
where 客户编号='20130001'



  例3:(after类型触发器)(当“商品管理数据库”中有商品售出时,除了向“销售信息表”中添加销售记录之外,还应该为“库存信息表”中相应商品减掉与销售数量等值的库存数量。创建名为“t_销售表_库存表”的触发器。)
use 商品管理数据库
go
create trigger t_销售表_库存表
on 销售信息表
after
insert
as
update 库存信息表
set 库存数量=库存数量-(select 销售数量 from inserted)

--查询“库存信息表”商品编号为11110003的商品现有数量
select*from 库存信息表 where 商品编号='11110003'
--向“销售信息表”添加商品编号wei11110003的商品售出10个记录,以激发存储过程。
use 商品管理数据库
go
insert 销售信息表 values(8,'11110003',2.5,10,50,'2012-12-21',20130004)
--查询“库存信息表”商品编号为11110003的商品现有数量,与之前作比较
select*from 库存信息表 where 商品编号='11110003'
理解例子

 --查看触发器

注:由于触发器是特殊的存储过程,因此可以使用系统存储过程查看触发器相关信息
exec 系统存储过程 用户自定义存储过程名
--sp_depends:查看触发器的依赖关系
--sp_help:查看触发器的创建信息
--sp_helptext:查看触发器的创建文本(被加密的触发器无法查看)


exec sp_helptrigger 数据表名
--sp_helptrigger:查看某张数据表中创建了那些类型的触发器

 

例:(查看“商品管理数据库”的“销售信息表”中创建了那些触发器,查看已创建的触发器信息)
use 商品管理数据库
go
exec sp_helptrigger 销售信息表

 

例:
use 商品管理数据库
go
exec sp_depends t_销售表_库存表
exec sp_help t_销售表_库存表
exec sp_helptext t_销售表_库存表

 

--修改触发器
注:无论是修改触发器名称还是功能,都会对该触发器有关联的数据表数据表中的字段产生影响,因此请根据需要谨慎修改。
  类似于修改存储过程,修改触发器也相当于删除了原来的触发器功能,创建了新的触发器功能

1)
alter trigger 触发器名
on 数据表名
[with encryption]
after | instead of [insert] [, update] [, delete]
as
T-SQL语句

2)
也可以使用系统存储过程sp_rename为触发器重命名:
exec sp_rename 原触发器名,新触发器名

 

--启用触发器
enable trigger 触发器名 on 数据表名

 

例:(启用“销售信息表”中的触发器“t__销售表_库存表”)
use 商品管理数据库
go
disable trigger t__销售表_库存表 on 销售信息表

 

--禁用触发器
disable trigger 触发器名 on 数据表名

 

例:(禁用“销售信息表”中的触发器“t__销售表_库存表”)
use 商品管理数据库
go
disable trigger t__销售表_库存表 on 销售信息表

 

--删除触发器
drop trigger 触发器名

 

例:
use 商品管理数据库
go
drop trigger t_销售表_库存表

 


 注:"--"可看成说明或者注释文本