首页 > 代码库 > SqlServer——触发器
SqlServer——触发器
一：触发器基本知识
触发器是一种特殊的存储过程，但没有接口（输入输出参数），触发器是引发它们的事务的一部分，因为SqlServer自动将引发它们的SQL语句和触发器作为一个隐式事务，因此当触发器正确执行后，事务才认为是完整的，触发器也可已在触发器内部通过 rollback tran 来回滚事务，当触发的SQL语句是隐式事务时，只回滚引发触发器的SQL语句和触发器；是显式事务时，将撤销从最外层 begin tran 开始的所有操作。所以触发器通常用来实现对表实施复杂的完整性约束和强制业务规则，它是一种高级约束，定义在特定的表上，却引用其它表中的列。可以定义比用check约束更为复杂的约束。例如，无法用约束实现的强制完整性规则，审核，维护不规范数据。
1、触发器的分类
触发器从 SQL 语句类型可分为DML触发器和DDL触发器。
- DML触发器：在往表中 insert、update、delete 时被自动地触发；DML 触发器从触发作用可分为 Instead of 触发器 和 After 触发器
- Instead of 触发器：也称为替代触发器，只能用与将触发器内部的代码替换用户提交的 DML 操作（ insert、update、delete ），即只执行触发器代码，不执行用户提交的代码。作用于 表 或 视图；一种操作只能有一个 instead of 触发器。
- After 触发器：在响应用户代码（DDL和DML操作均可）之后执行触发器代码。该触发器按语句触发而不是按行触发，即一条语句不管影响了多少行，After 触发器只能触发一次；并且只能作用于 表；一种操作可以有多个 After 触发器，使用sp_settriggerorder存储过程标记最先执行和最后执行的触发器；如果触发的 SQL语句违反了约束，则不触发After触发器，而由于Instead of 触发器在执行触发 SQL语句前触发，却可以。所以Instead of 触发器可以对约束进行一些预处理。
- DDL触发器：对表、视图、存储过程等的 create、drop、alter 时触发。
2、触发器的 Inserted 表和 Deleted 表
这两个表由系统来维护﹐它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行 完成后﹐与该触发器相关的这两个表也被删除。Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。 Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。用户可以用SELECT语句查询这两个临时表，但不允许进行修改。
Inserted 表和 Deleted 表没有索引，因此当表内数据较多且频繁引用表内数据时，应当将数据保存到一个临时表，为临时表创建索引。
比如我在表上制定了一个后触发器当插入了数据后往另外一个表也插入这条数据； 当我执行insert的一条数据后执行触发器的语句，往另外一个表插入数据； 问题来了 当我连续插入10条数据触发器的运行机制又是怎样的呢? SqlServer与MySql不同的点在于SqlServer在执行完10条插入Sql语句才允许触发器内的语句.
| 对表的操作 | Inserted | Update | Deleted |
| Inserted 表 | 插入的新数据 | 更新后的数据 | X |
| Deleted 表 | X | 更新前的数据 | 删除的数据 |
3、受影响的行数。
如下语句：
select @c1=c1,@c2=c2,@c3=c3 from Inserted
如果并没有一行受到触发 触发器 的SQL语句的影响，那么变量@c1、@c2、@c3的值不会改变，仍然是原来的值，而当有多个行受到影响时，每一行都会对变量值进行修改，到最后，变量值只是最后一行的值。因此要先判断受影响的行数。
@@rowcount 函数 保存着上一条语句受影响的行数，或者用 select @count=count（1） from Inserted 来判断Inserted 表中的行数。
例1:
create trigger tr_1
on database
for Create_Table --|| Drop_Table
as
raiserror(‘你不能创建表‘,16,1)
rollback --可以进行回滚因为Create_table 是隐式事务
drop trigger tr_1 on database –-删除触发器时必须有on database指明是删除数据库上的触发器
DML触发器:
当数据库操作中发生数据操作语言(DML)事件时,将调用DML触发器.
DML触发器可以分为以下3种类型:
1.AFTER 触发器:AFTER 触发器只能在表上指定 (后触发)
在执行Insert Update Delete 语句操作之后执行 AFTER 触发器,制定AFTER与制定For相同.
2.INSTEAD OF触发器 (替代触发)
可以对视图或表定义但一个 INSTEAD OF Insert/Update/Delete触发器 只能有一个
3.CLR触发器.可以是上面2种或者DDL触发器
DML触发器细分为4种类型
Insert触发器
Delete触发器
Update触发器
以上几种类型的混合配饰触发器
①一个表可以有多个后触发器,但只能有一个替代触发器 视图上只能有替代触发器不能有后触发器
AFTER 触发器(后触发)
实例1:
if exists(select * from sysobjects where name=‘tr_3‘)
drop trigger tr_3
Go
Create trigger tr_3
on Employee
for update –-当执行完了update语句后才会跳到我们后面定义的语句块
as
if update(CardID) –-判断是否对某一列进行了更改
raiserror(‘你不更改卡号ID‘,16,1)
实例2:
if exists(select * from sysobjects where name=‘tr_4‘)
drop trigger tr_4
Go
Create trigger tr_4
on Employee
for delete
as
raiserror(‘不能删除员工‘,16,1)
②所谓的后触发就是执行完了对应的操作才执行我们定义的触发器内的语句块
INSTEAD OF触发器 (替代触发) :
实例1:
if exists(select * from sysobjects where name=‘tr_5‘)
drop trigger tr_5
Go
Create trigger tr_5
on Employee
instead of delete –替代了删除事件
as
select * from deleted --看看临时deleted表里面有什么
delete from pay where CardID in (select CardID from deleted) --先删子表再删基表
执行delete from Employee where CardID in (select CardID from deleted)
③[所谓的替代触发就是不执行对应的操作而执行我们定义触发器内的语句块
触发器使用的inserted临时表和deleted临时表:[无论是后触发还是替代触发都产生这2个临时表]:
虚拟表Inserted 虚拟表Deleted
执行Update时 将更新数据放到Inserted临时表 将原来的数据放进Deleted的临时表
执行Insert时 将插入的数据放到Inserted临时表 /
执行Delete时 / 将删除的数据放到deleted临时表
l.两个表的结构与激活触发器的原数据表结构相同。
2. 用INSERT语句插入记录激活触发器时，系统在原表插入记录的同时，也自动把记录插入到inserted临时表。
3. 用Delete语句删除记录激活触发器时，系统在原表删除记录的同时，会把删除的记录添加到deleted临时表。
4. 用UPDATE语句修改数据激活触发器时，系统先在原表删除原有记录，删除的记录被添加到deleted临时表，然后再插入新记录，并同时插入到inserted临时表。
5. 用户可以用SELECT语句查询这两个临时表，但不允许进行修改。
6. 触发器一旦执行完成，这两个表将被自动删除。
SqlServer的触发原理是:
比如我在表上制定了一个后触发器当插入了数据后往另外一个表也插入这条数据
当我执行insert的一条数据后 执行触发器的语句 往另外一个表插入数据
问题来了 当我连续插入10条数据 触发器的运行机制又是怎样的呢?
SqlServer与MySql不同的点在于SqlServer在执行完10条插入Sql语句才允许触发器内的语句.
而MySql是行级触发 也就是插入一条数据就执行触发器内的语句
SqlServer——触发器