sqlserver触发器(trigger)

触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。

触发器分类

1、DML（数据操纵语言 Data Manipulation Language）触发器：是指触发器在数据库中发生 DML 事件时将启用。DML事件是指在表或视图中对数据进行的 insert、update、delete 操作的语句。
2、DDL（数据定义语言 Data Definition Language）触发器：是指当服务器或数据库中发生 DDL 事件时将启用。DDL事件是指在表或索引中的 create、alter、drop 操作语句。
3、登陆触发器：是指当用户登录 SQL SERVER 实例建立会话时触发。如果身份验证失败，登录触发器不会触发。
其中 DML 触发器比较常用，根据 DML 触发器触发的方式不同又分为以下两种情况：
after 触发器（之后触发）：其中 after 触发器要求只有执行 insert、update、delete 某一操作之后触发器才会被触发，且只能定义在表上。
instead of 触发器（之前触发）：instead of 触发器并不执行其定义的操作（insert、update、delete）而仅仅只是执行触发器本身。可以在表或视图上定义 instead of 触发器。
for触发器，它的作用默认是和after一样的。
DML 触发器有两个特殊的表：插入表（instered）和删除表（deleted），这两张表是逻辑表。这两个表是建立在数据库服务器的内存中，而且两张表的都是只读的。这两张表的结构和触发器所在的数据表的结构是一样的。
当触发器完成工作后，这两张表就会被删除。
Inserted 表的数据是插入或是修改后的数据，而 deleted 表的数据是更新前的或是已删除的数据。
注意：没有update表的说法，因为update操作可以认为是先删除(delete)，再插入(insert)，所以deleted表存储更新前的数据，inserted表存储更新后的数据。

语法

CREATE TRIGGER trigger_name //触发器名
ON { table | view } //视图或表
[ WITH ENCRYPTION ] //加密
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ DELETE ] [ UPDATE ] }
[ WITH APPEND ] //指定应该添加现有类型的其它触发器
[ NOT FOR REPLICATION ] //表示当复制进程更改触发器所涉及的表时，不应执行该触发器。
AS //是触发器要执行的操作。
[ { IF UPDATE ( column ) //测试在指定的列上进行的 INSERT 或 UPDATE 操作，不能用于 DELETE 操作。
[ { AND | OR } UPDATE ( column ) ] //是要测试 INSERT 或 UPDATE 操作的列名
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) updated_bitmask ) //IF ( COLUMNS_UPDATED ( )测试是否插入或更新了提及的列，仅用于 INSERT 或 UPDATE触发器中 updated_bitmask是用于比较运算的位运算符。
column_bitmask [ ...n ] //是整型位掩码，表示实际更新或插入的列
} ]
sql_statement [ ...n ] //是比较运算符。
}
}

简化语法

1、触发器新增

原理：
当触发INSERT触发器时，新的数据行就会被插入到触发器表和inserted表中。inserted表是一个逻辑表，它包含了已经插入的数据行的一个副本。inserted表包含了INSERT语句中已记录的插入动作。inserted表还允许引用由初始化INSERT语句而产生的日志数据。触发器通过检查inserted表来确定是否执行触发器动作或如何执行它。inserted表中的行总是触发器表中一行或多行的副本。
场景：增加学生信息时，要校验其年龄，暂定其年龄必须大于18，否则新增失败
作用：校验约束
具体实例：

INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(105,'李四',16,'BeiJing',11)

2、触发器更新

原理：

可将UPDATE语句看成两步操作：即捕获数据前像(before image)的DELETE语句，和捕获数据后像(after image)的INSERT语句。当在定义有触发器的表上执行UPDATE语句时，原始行（前像）被移入到deleted表，更新行（后像）被移入到inserted表。

触发器检查deleted表和inserted表以及被更新的表，来确定是否更新了多行以及如何执行触发器动作。

可以使用IF UPDATE语句定义一个监视指定列的数据更新的触发器。这样，就可以让触发器容易的隔离出特定列的活动。当它检测到指定列已经更新时，触发器就会进一步执行适当的动作，例如发出错误信息指出该列不能更新，或者根据新的更新的列值执行一系列的动作语句。

场景：

专业信息ID修改，对应的学生信息中专业ID也相应进行修改

实例实现：

3、触发器删除

原理：

当触发DELETE触发器后，从受影响的表中删除的行将被放置到一个特殊的deleted表中。deleted表是一个逻辑表，它保留已被删除数据行的一个副本。deleted表还允许引用由初始化DELETE语句产生的日志数据。

使用DELETE触发器时，需要考虑以下的事项和原则：

当某行被添加到deleted表中时，它就不再存在于数据库表中；因此，deleted表和数据库表没有相同的行。
创建deleted表时，空间是从内存中分配的。deleted表总是被存储在高速缓存中。
为DELETE动作定义的触发器并不执行TRUNCATE TABLE语句，原因在于日志不记录TRUNCATE TABLE语句。
场景：学校某选修课取消。

处理逻辑：在删除课程的同时，需要删除该课程的选课信息。

触发器：

4、instead of 触发器

执行上面的语句之后，咦，数据怎么一点变化都没有？看看上面的介绍。instead of 触发器是之前触发。
instead of 触发器并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身，并且会覆盖触发语句的操作，即 after 触发器 T-SQL 语句的操作，
很明显我们上面定义的表 Student 的 after 触发器也没有效果了，现在理解了这句话了吧。

对表的操作	Inserted逻辑表	Deleted逻辑表
增加记录(insert)	存放增加的记录	无
删除记录(delete)	无	存放被删除的记录
修改记录(update)	存放更新后的记录	存放更新前的记录