SQL SERVER触发器

sqlserver触发器详解

–触发器是一系列的T-SQL编写完成，是一种特殊的存储过程，
–触发器只能由数据库的特定事件来触发，所谓的的特定事件主要包括以下几种类型
(1)用户在指定的表或视图中进行DML操作
INSERT 操作，在特定的表或视图中添加数据
UPDATE 操作，对特定的表或视图修改数据
DELETE 操作，删除特定的表或视图数据
(2)用户进行DDL操作
CREATE 创建对象
ALTER 修改对象
DROP 删除对象
(3)用户进行LOGON(登录)操作
与SQL SERVER 实例建立连接
–触发器分类
DML触发器：又被称为数据操作语言触发器作用在表或视图上，在对表或视图操作时会激发该类型触发器
该类型触发器包括
AFTER触发器：在 INSERT,UPDATE,DELETE,执行操作后触发，
INSTEAD OF 触发器：此类型触发器会用触发器本身操作替换原来的触发器（INSERT…）通常作用在视图上，利用该触发器视图可以变成可更新视图。
DDL触发器：被称为定义语言触发器，当 CREATE，ALTER，DROP 队象进行其他DDL操作时会激发该触发器，利用它可以影响数据库业务规则。
CLR触发器：在 .NET FRAMEWORK中创建，不像其他触发器需要执行T-SQL过程
登录触发器：当于SQL SERVER实例建立连接时触发
–触发器工作原理
触发器的原理涉及到两张虚拟表，分别是INSERTED表和DELETED表
INSERT 操作触发器当操作数据时，会在数据表和 INSERTED 表同时放入数据，利用INSERTED表可以得到已经插入的数据
DELETE 操作触发器。当从数据库删除数据时。数据首先被放入DELETED表中，该表是一张存放了已经删除的数据虚拟表。在触发器中可以调用该表的数据。
UPDATE 操作触发器。和其他两种不同，当对触发器所在的表执行UPDATE语句时，元数据会被转移到DELETED表中，而修改后的数据则被插入到INSERTED表中最后
触发这两个表的数据，进行更新。

(1)数据操纵语言DML触发器的语法结构

  CREATE TRIGGER [dbo.]triggeres
  on{table|view}
  {WITH<trigger_option>[,...n]}
  {FOR|AFTER|INSERT OF}
  {[INSERT][,][UPDATE][,][DELETE]}
  [WITH APPEND]
  [NOT FOR REPLICATION]
  AS {SQL_STATEMENT[;][....n]|EXTERNAL NAME assembly_name.class_name.method_name[;]}
  <trigger_option> ::=
  [ENCRYPTION]
  [EXECUTE AS Clause]

–语法说明
第1行：CREATE TRIGGER表示创建触发器的关键字 dbo项表示触发器所属的架构名称 triggeres表示触发器名称
第2行 on{table|view},触发器所作用的表或视图，DML类型触发器不能作用在局部或全局临时表上
第3行 FOR|AFTER 项：AFTER 表示触发器被激发的时机。它在SQL所有的操作中完成，并且约束检查完成后被激发，默认是 AFTER
INSERT OF项：表示替代类型触发器对每个 INSERT, UPDATE 或 DELETE 语句只能定义一个 insert of 触发器
第4行 [INSERT][,][UPDATE][,][DELETE]项：激发触发器的操作，这里可以选取任意组合。
第5行 WITH APPEND 项：指定添加一个已有类型的触发器，但如果显示声明了 AFTER 触发器，或触发器类型是 insert of 时则不能使用该项
第6行 NOT FOR REPLICATION 项：当复制代码修改涉及触发器表时，不执行触发器。
第7行 SQL_STATEMENT 项：可以确定触发器的具体操作的判断条件和操作 EXTERNAL NAME assembly_name.class_name.method_name[;]项：针对CLR触发器，指定
程序集与触发器绑定方法。
[ENCRYPTION] 项： 表示对创建触发器的语句进行模糊处理。
EXECUTE AS 项：指定用于执行该触发器的安全上下文。
–数据库定义语言DDL触发器语法结构

CREATE TRIGGER TRIGGER
  ON {ALL SERVER|DATABASE }
  {WITH<DDL_trigger_option>[,...n]}
   {FOR|AFTER}{event_TYPE|event_group}[,...n]
as {SQL_STATEMENT[;][....n]|EXTERNAL NAME assembly_name.class_name.method_name[;]}
  <trigger_option> ::=
  [ENCRYPTION]
  [EXECUTE AS Clause]

第1行：CREATE TRIGGER:表示创建触发器的关键字 TRIGGER 表示触发器名称
第2行 ON {ALL SERVER| },表示触发器的范围是整个服务器 ON {ALL DATABASE| }表示触发器的范围是当前数据库
第3行 FOR|AFTER 项：AFTER 表示触发器被激发的时机。默认是 AFTER
第5行 event_TYPE项：激发触发器DDL事件名称 event_group：预定义T-SQL语言事件分组名称
第6行 SQL_STATEMENT项：可以确定触发器具体操作的判断条件和操作，EXTERNAL NAME assembly_name.class_name.method_name[;]项：针对CLR触发器，指定
程序集与触发器绑定方法。
第7，8，9行
[ENCRYPTION] 项： 表示对创建触发器的语句进行模糊处理。
–登录触发器语法结构

 CREATE TRIGGER trigger
  on ALL SERVER
  {WITH<LOGON_trigger_option>[,...n]}
  {FOR|AFTER}LOGON
  as {SQL_STATEMENT[;][....n]|EXTERNAL NAME assembly_name.class_name.method_name[;]}
  <trigger_option> ::=
  [ENCRYPTION]
  [EXECUTE AS Clause]

FOR|AFTER 项 ：默认是after这里表示登录后被触发