select不能触发change_从一个案例来看sqlserver数据库INSTEAD OF 与AFTER 触发器区别

概述

最近在写触发器时碰到一个报错，简单记录如下：报错信息：

> [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]不能在 'inserted' 表和 'deleted' 表中使用 text、ntext 或 image 列。 (311)

下面记录下解决的思路和方法，仅供参考。

---

思路

不能比较或排序 text、ntext 和 image 数据类型,这里除非使用 IS NULL 或 LIKE 运算符，这里改成 instead of 就可以在触发器中访问二进制字段了,也不需要去根据id去查

所以这里只需要把其中的“AFTER” 改成 “instead of” 就可以了。

调整后：

---

那么AFTER和instead of有什么区别呢？

1、INSTEAD OF 触发器

INSTEAD OF 触发器用来代替通常的触发动作，即当对表进行INSERT、UPDATE 或 DELETE 操作时，系统不是直接对表执行这些操作，而是把操作内容交给触发器，让触发器检查所进行的操作是否正确。如正确才进行相应的操作。因此，INSTEAD OF 触发器的动作要早于表的约束处理。

INSTEAD OF 触发器的操作有点类似于完整性约束。在对数据库的操纵时，有些情况下使用约束可以达到更好的效果，而如果采用触发器，则能定义比完整性约束更加复杂的约束。

INSTEAD OF 触发器不仅可在表上定义，还可在带有一个或多个基表的视图上定义，但在作为级联引用完整性约束目标的表上限制应用。

2、AFTER 触发器

AFTER 触发器定义了对表执行了 INSERT、UPDATE 或 DELETE 语句操作之后再执行的操作。比如对某个表中的数据进行了更新操作后，要求立即对相关的表进行指定的操作，这时就可以采用 AFTER 触发器。AFTER 触发器只能在表上指定，且动作晚于约束处理。

每一个表上只能创建一个 INSTEAD OF 触发器，但可以创建多个 AFTER 触发器。

3、实例:

INSTEAD OF 触发器:

向表“计0261”插入数据时，检查学号是否存在于表“计026”中，如存在则进行插入操作，否则就不插入。

CREATE TRIGGER [checkid] ON [dbo].[计0261] INSTEAD OF insertASIF NOT EXISTS(SELECT * FROM 计026 WHERE 学号=(SELECT 学号 FROM INSERTED)) BEGIN ROLLBACK TRANSACTION PRINT '要处理记录的学号不存在！' ENDELSE BEGIN INSERT INTO 计0261 select * from inserted PRINT '已经成功处理记录！'END

AFTER 触发器:

对订货表设置 AFTER(FOR) 类型的 INSERT 触发器，用来在插入记录时自动将统计值计算到订货统计表中。

CREATE TRIGGER [orderinsert] ON [dbo].[订货表]AFTER INSERTASDECLARE @bookid int, @ordernum int, @num intSELECT @bookid = 书籍编号, @ordernum = 数量 FROM INSERTEDSELECT @num = count(书籍编号) FROM 订货统计表 WHERE 书籍编号=@bookidIF @num = 0 --未找到该书，插入记录 INSERT INTO 订货统计表 VALUES(@bookid, @ordernum)ELSE --找到该书，更新记录 UPDATE 订货统计表SET 总订货量 = 总订货量 + @ordernum WHERE 书籍编号 = @bookid

---

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~