[摘录] DML 触发器的类型

 

DML 触发器的类型

AFTER 触发器
在执行 INSERT、UPDATE、MERGE 或 DELETE 语句的操作之后执行 AFTER 触发器。 如果违反了约束，则永远不会执行 AFTER 触发器；因此，这些触发器不能用于任何可能防止违反约束的处理。 对于在 MERGE 语句中指定的每个 INSERT、UPDATE 或 DELETE 操作，将为每个 DML 操作触发相应的触发器。

INSTEAD OF 触发器
INSTEAD OF 触发器替代下列触发语句的标准操作。 因此，触发器可用于对一个或多个列执行错误或值检查，然后在插入、更新或删除行之前执行其他操作。 例如，当在工资表中小时工资列的更新值超过指定值时，可以将触发器定义为产生错误消息并回滚该事务，或在将记录插入工资表中之前将新记录插入到审核记录。 INSTEAD OF 触发器的主要优点是可以使不能更新的视图支持更新。 例如，基于多个基表的视图必须使用 INSTEAD OF 触发器来支持引用多个表中数据的插入、更新和删除操作。 INSTEAD OF 触发器的另一个优点是使你得以编写这样的逻辑代码：在允许批处理的其他部分成功的同时拒绝批处理中的某些部分。

下表对 AFTER 触发器和 INSTEAD OF 触发器的功能进行了比较。

函数	AFTER 触发器	INSTEAD OF 触发器
适用性	表	表和视图
每个表或视图包含触发器的数量	每个触发操作（UPDATE、DELETE 和 INSERT）包含多个触发器	每个触发操作（UPDATE、DELETE 和 INSERT）包含一个触发器
级联引用	无任何限制条件	不允许在作为级联引用完整性约束目标的表上使用 INSTEAD OF UPDATE 和 DELETE 触发器。
执行	之后： 约束处理 声明性引用操作 创建插入的 和 删除的 表 触发操作	之前：约束处理 代替：触发操作 之后：创建 插入的 和 删除的 表
执行顺序	可指定第一个和最后一个执行	不适用
插入的和 删除的表中的 varchar(max) 、 nvarchar(max) 和 varbinary(max) 列引用	允许	允许
插入的和 删除的表中的 text 、 ntext 和 image 列引用。	不允许	允许

CLR 触发器
CLR 触发器可以是 AFTER 触发器或 INSTEAD OF 触发器。 CLR 触发器还可以是 DDL 触发器。 CLR 触发器将执行在托管代码（在 .NET Framework 中创建并在 SQL Server 中上载的程序集的成员）中编写的一个或多个方法，而不用执行 Transact-SQL 存储过程。

 

 

以下部分节选自 https://blog.csdn.net/weixin_43494908/article/details/89240861

SQL Server为每个触发器都创建了两个专用表﹕Inserted表和Deleted表。这两个表由系统来维护﹐它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行 完成后﹐与该触发器相关的这两个表也被删除。
Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。
Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。

Instead of 和 After触发器
SQL Server2000提供了两种触发器﹕Instead of 和After 触发器。

Instead of触发器用于替代引起触发器执行的T-SQL语句。除表之外﹐Instead of 触发器也可以用于视图﹐用来扩展视图可以支持的更新操作。

After触发器在一个Insert,Update或Deleted语句之后执行﹐进行约束检查等动作都在After触发器被激活之前发生。After触发器只能用于表。

一个表或视图的每一个修改动作(insert,update和delete)都可以有一个instead of 触发器﹐一个表的每个修改动作都可以有多个After触发器。

 

inserted表和deleted表用于存放对表中数据行的修改信息，他们是触发器执行时自动创建的，放在内存中，是临时表。当触发器工作完成，它们也被删除。它们是只读表，不能向它们写入内容。
　　　inserted表：用来存储INSERT和UPDATE语句所影响的行的副本。意思就是在inserted表中临时保存了被插入或被更新后的记录行。在执行 INSERT 或UPDATE 语句时，新加行被同时添加到inserted表和触发器表中。因此，可以从inserted表检查插入的数据是否满足需求，如不满足则回滚撤消操作。
　　　deleted表：用来存储DELETE和UPDATE语句所影响行的副本。意思是在delete表中临时保存了被删除或被更新前的记录行。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传到deleted表中。所以可以从deleted表中检查删除的数据行是否能删除。

当表建立触发器后：

　　1.插入操作（Insert）
　　Inserted表有数据，Deleted表无数据

　　2.删除操作（Delete）
　　Inserted表无数据，Deleted表有数据

　　3.更新操作（Update）
　　Inserted表有数据（新数据），Deleted表有数据（旧数据）

 

Instead Of触发器与After触发器的工作流程是不一样。

After触发器是在SQLSERVER服务器接到执行SQL语句请求之后，先建立Inserted和Updated临时表，然后在更改物理表上的数据，最后才激活触发器程序。

Instead Of触发器在SQLSERVER服务器接到执行SQL语句请求，建立Inserted和Updated临时表后就激活了Instead Of触发器程序，至于SQL语句的请求如何操作数据就不在管了，把执行权全权叫给了Instead Of触发器。

使用T-SQL语句来创建触发器
  
基本语句如下﹕

create trigger trigger_name
on {table_name | view_name}
{for | After | Instead of }
[ insert, update,delete ]
as
sql_statement

相关示例﹕
1﹕在Orders表中建立触发器﹐当向Orders表中插入一条订单记录时﹐检查goods表的货品状态status是否为1(正在整理)﹐是﹐则不能往Orders表加入该订单。

create trigger orderinsert
on orders
after insert
as
if (select status from goods,inserted
where goods.name=inserted.goodsname)=1
begin
print ‘the goods is being processed’
print ‘the order cannot be committed’
rollback transaction --回滚﹐避免加入
end

2﹕在Orders表建立一个插入触发器﹐在添加一条订单时﹐减少Goods表相应的货品记录中的库存。

create trigger orderinsert1
on orders
after insert
as
update goods set storage=storage-inserted.quantity
from goods,inserted
where
goods.name=inserted.goodsname

3﹕在Goods表建立删除触发器﹐实现Goods表和Orders表的级联删除。

create trigger goodsdelete
on goods
after delete
as
delete from orders
where goodsname in
(select name from deleted)

4﹕在Orders表建立一个更新触发器﹐监视Orders表的订单日期(OrderDate)列﹐使其不能手工修改.

create trigger orderdateupdate
on orders
after update
as
if update(orderdate)
begin
raiserror(’ orderdate cannot be modified’,10,1)
rollback transaction
end

5﹕在Orders表建立一个插入触发器﹐保证向Orders表插入的货品名必须要在Goods表中一定存在。

create trigger orderinsert3
on orders
after insert
as
if (select count(*) from goods,inserted where goods.name=inserted.goodsname)=0
begin
print ’ no entry in goods for this order’
rollback transaction
end