SQL Server的触发器

SQL Server的Trigger简介

SQL Server主要有三种trigger: DDL Trigger, DML Trigger, Logon Trigger.下面将分别介绍。
SQL Server的Logon Trigger是触发器在用户建立会话后将触发对应的存储过程。更加具体一点是在用户真正建立会话之前，认证成功之后的阶段。认证失败的时候触发器是不会触发的。
我们可以使用logon trigger来审计和控制服务端会话，比如追溯登入活动，限制特定账号的登入会话数。
实际上logon trigger对应于AUDIT_LOGIN事件，AUDIT_LOGIN可以用于Event Notifications 。
Event Notifications和Trigger的主要区别是，触发器是同步的，Event Notification是异步的。

DDL Trigger是在各种DDL(Data Definition Language)事件时触发的存储过程。这些事件主要包括事务语句：CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS. 某些系统存储过程运行DDL类似的操作也可以触发DDL触发器。 一般我们使用DDL Trigger来：

• 防止数据库结构改变
• 结构变化需要对应一些改变
• 记录结构改变

DML Trigger是当DML(Data Manipulation Language)事件发生时触发的存储过程。DML事件包含: INSERT, UPDATE, DELETE语句。DML trigger可以被用来保证业务规则和数据完整性, 尤其在底层一些contraints不能很好满足需要的时候。

Logon Trigger

触发器的执行顺序

如果在LOGON事件定义了多个触发器， 触发器执行的先后顺序是可以在sp_settriggerorder中定义的。SQL Server不能保证其它触发器的执行顺序。

管理事务

在SQL Server真正触发trigger前，SQL Server会创建一个隐式的事务，此时事务计数为1.在所有logon trigger完成执行，事务才会commit. 当logon trigger执行完，事务计数为0时，SQL Server会报错。什么时候logon trigger会将事务计数置0呢？一般有两种情况，ROLLBACK TRANSACTION和不正确的COMMIT TRANSACTION数(每次commit会减一计数).
在logon trigger使用ROLLBACK时需要注意：

• 任何rollback前的数据修改将被回滚，包括在同一个事件之前已执行的触发。后续的trigger不会再触发。
• 注意，当前triggerROLLBACK后面的语句将会继续执行，数据修改将不会被回滚。

DDL Trigger

DDL Trigger的类型

DDL Trigger有两种类型： 事务型(Transact-SQL)和通用语言型(Common Language Runtime). 事务型的DDL Trigger应对服务级别或者数据库级别的事件，如修改服务配置(ALERT SERVER CONFIGUATION)或删除表(DROP TABLE);CLR Trigger是在.net写的assembly代码。

DDL Trigger的范围

DDL Trigger触发对应的事件可以是当前数据库或者当前服务器，取决于具体事件。数据库范围的DDL Trigger存储在数据库上, 运行select * sys.triggers视图可以看到数据库层面的DDL Trigger;服务器层面的DDL Trigger存储在master数据库，运行select * from sys.server_triggers可以查看服务器层面的DDL Trigger。

DML Trigger

DML Trigger的类型

DML Trigger有三种trigger：AFTER Trigger,INSTEAD OF Trigger和CLR Trigger.

• After Trigger是在INSERT, UPDATE, MERGE, DELETE语句运行后触发的触发器。如果发生constraint, 这个trigger是不会触发的。
• INSTEAD OF trigger覆盖标准的触发器语句，所以可以用来处理错误或者在insert, update, delete之前做数值检查。这个触发器主要的两个优势是能让不能修改的视图支持更新，另一个优势是可以让你的批处理部分处理成功，部分处理失败。
• CLR Trigger

AFTER触发器和INSTEAD OF触发器的区别: