INSTEAD OF 触发器

最新推荐文章于 2021-02-27 12:57:40 发布

张炜的技术博客

最新推荐文章于 2021-02-27 12:57:40 发布

阅读量3.6k

点赞数

分类专栏： DB数据库文章标签： insert sql server sql delete 服务器产品

本文链接：https://blog.csdn.net/e_online/article/details/5984675

版权

DB数据库专栏收录该内容

30 篇文章 0 订阅

订阅专栏

Instead Of触发器与After触发器的工作流程是不一样的。After触发器是在SQL Server服务器接到执行SQL语句请求之后，先建立临时的Inserted表和Deleted表，然后实际更改数据，最后才激活触发器的。而Instead Of触发器看起来就简单多了，在SQL Server服务器接到执行SQL语句请求后，先建立临时的Inserted表和Deleted表，然后就触发了Instead Of触发器，至于那个SQL语句是插入数据、更新数据还是删除数据，就一概不管了，把执行权全权交给了Instead Of触发器，由它去完成之后的操作。

Instead Of触发器的使用范围

Instead Of触发器可以同时在数据表和视图中使用，通常在以下几种情况下，建议使用Instead Of触发器：

>> 数据库里的数据禁止修改：例如电信部门的通话记录是不能修改的，一旦修改，则通话费用的计数将不正确。在这个时候，就可以用Instead Of触发器来跳过Update修改记录的SQL语句。

>> 有可能要回滚修改的SQL语句：用After触发器并不是一个最好的方法，如果用Instead Of触发器，在判断折扣大于0.6时，就中止了更新操作，避免在修改数据之后再回滚操作，减少服务器负担。

>> 在视图中使用触发器：因为After触发器不能在视图中使用，如果想在视图中使用触发器，就只能用Instead Of触发器。

>> 用自己的方式去修改数据：如不满意SQL直接的修改数据的方式，可用Instead Of触发器来控制数据的修改方式和流程。

一个例子：

--创建表

CREATE TABLE [tt] (
[a] [int] NOT NULL ,
[b] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[c] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tt] PRIMARY KEY CLUSTERED
(
[a]
) ON [PRIMARY]
) ON [PRIMARY]
GO

--创建触发器

CREATE TRIGGER ta ON [dbo].[tt]
INSTEAD OF INSERT
AS
SET NOCOUNT ON
if (SELECT a from inserted)>3
print N'不能插入大于3的'
else
insert into tt select * from inserted

--执行

INSERT INTO tt values(5,'a','aa')

select * from tt

可以看到insert语句并没有把数据插入进去。

------------------------------------------------------------------------------------------------

虽然视图通常不能动态修改，但是，使用 INSTEAD OF 触发器您可以指定操作，保持视图最新，同时修改视图基础基表中的数据。例如，您可在视图上定义 INSTEAD OF INSERT 触发器，以替换标准的 INSERT 语句。

假定在 pubs 数据库中以下面的视图开始：

CREATE VIEW AuthorsNames
AS
SELECT au_id, au_fname, au_lname
FROM authors

如果直接对 authors 表应用 INSERT 事件，则该视图将是不正确的，因为没有向该视图通知新插入的作者。避免此问题的方法是在该视图上创建 INSTEAD OF 触发器来处理插入。

CREATE TRIGGER ShowInsert on AuthorsNames
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO authors
   SELECT address, au_fname, au_id, au_lname, city, contract, phone, state, zip
   FROM inserted
END

创建结构

CREATE TRIGGER <触发器名>
ON <表名 | 视图名>
{FOR | AFTER | INSTEAD OF} {[INSERT] [,UPDATE] [,DELETE]}
AS
{ [IF UPDATE(列名)] [{AND | OR} UPDATE (列名)] }

收集2

DML触发器是表上的程序,主要提供数据添加、修改与删除后的程序处理方法，可以用来检查数据及进行数据更新，也可以分担一些前端应用程序撰写的逻辑规则。
触发器英文名为trigger，也就是板机的意思，与枪支的操作原理类似，只有扣动板机，子弹才会飞出。将该词用在SQL SERVER环境中，表示当表发生了insert 、update、delete操作之后，才会执行的t-SQL程序，这样的程序就叫触发器。

应用场景: 触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于强制复杂的业务规则或要求。
触发器的主要应用场合概括起来讲有以下几种：
    1.当向一张表中添加或删除记录时，需要在相关表中进行同步操作。比如，当为应用系统添加一个系统用户时，需要同时向权限表中添加该用户的缺省权限，此时就编写系统用户表的触发器在添加记录动作时触发。
    2.当表上某列数据的值与其他表中的数据有联系时。比如，当某客户进行欠款消费，可以在生成订单时通过设计触发器判断该客户的累计欠款是否超出了最大限度。
    3.当需要对某张表进行跟踪时。比如，当人事表中有人离职时，第一时间通知或更改相关表的值。

DML触发器的分类：SQL Server 2005的DML触发器分为两类：
        * After触发器：这类触发器是在记录已经改变完之后（after），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用Rollback Transaction语句来回滚本次的操作。
        * Instead Of触发器：这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作（Insert、Update、Delete），而去执行触发器本身所定义的操作。

应用示例：alter

Instead Of

 
   01--例一：以下是一个包含提醒电子邮件的触发器例子，如果订单表里记录有改动的的话（无论增加订单还是修改、删除订单），则给物流人员张三发送电子邮件： 
 
   02  
 
   03CREATE TRIGGER 订单_Insert 
 
   04ON 订单 
 
   05AFTER INSERT, UPDATE, DELETE
 
   06AS
 
   07   EXEC master..xp_sendmail '张三', '订单有更改，请查询确定'
 
   08GO 
 
   09  
 
   10--例二：在订单明细表里，折扣字段不能大于0.6，如果插入记录时，折扣大于0.6的话，回滚操作。 
 
   11CREATE TRIGGER 订单明细_Insert 
 
   12   ON  订单明细 
 
   13   AFTER INSERT
 
   14AS
 
   15BEGIN
 
   16         if (Select 折扣 from inserted)>0.6 
 
   17         begin
 
   18              raiserror('折扣不能大于0.6',16,1) 
 
   19               Rollback Transaction --回滚事务。 
 
   20         end
 
   21END
 
   22GO 
 
   23  
 
   24--在示例二中运用了两个方法，一个是前面说过的，在Inserted表里查询某个字段，还有一个是用Rollback Transaction来回滚操作。如果用下面的SQL语句来进行Insert操作的话，插入记录将会不成功。 
 
   25INSERT INTO 订单明细(订单ID,产品ID,单价,数量,折扣)   
 
   26VALUES (11077,1,18,1,0.7)   
 
   27折扣为0.7, 插入记录不符合触发器里的约束，回滚操作，插入记录不成功。

after应用范围：只能使用在表上。

应用示例2：

Instead Of触发器的使用范围
Instead Of触发器可以同时在数据表和视图中使用，通常在以下几种情况下，建议使用Instead Of触发器：
* 数据库里的数据禁止修改：例如电信部门的通话记录是不能修改的，一旦修改，则通话费用的计数将不正确。在这个时候，就可以用Instead Of触发器来跳过Update修改记录的SQL语句。
* 有可能要回滚修改的SQL语句：如11.5.3节中的例二，用After触发器并不是一个最好的方法，如果用Instead Of触发器，在判断折扣大于0.6时，就中止了更新操作，避免在修改数据之后再回滚操作，减少服务器负担。
* 在视图中使用触发器：因为After触发器不能在视图中使用，如果想在视图中使用触发器，就只能用Instead Of触发器。
* 用自己的方式去修改数据：如不满意SQL直接的修改数据的方式，可用Instead Of触发器来控制数据的修改方式和流程。

资料引用：http://book.csdn.net/bookfiles/414/10041414953.shtml

应用技巧：
1、如何查看旧的记录？无论何时Insert 或者Update 语句影响一个或者多行时，inserted 临时表都有记录行。无论何时Delete 或者Update 语句影响一个或者多行时，deleted 临时表都有记录行。对于一个Update 语句，deleted 临时表有旧行，inserted 临时表有新行。
2、如何知道触发器修改了多少条记录？只要利用@@Rowcount这个系统变量就可以得知更新了多少条记录
3、如何知道插入记录的自动编号是多少？用@@IDENTITY可以获得刚插入记录的标识值
4、如何知道某个字段是否被修改？if update(列名)...
5、如何返回错误信息？Raiserror('除了折扣字段之外的其他字段信息不能修改',16,5)

以上内容可能有重复的,自己大脑自行过滤~

 
   01--前面说过的11.5.3节中的例二，用After触发器并不是一个最好的方法，如果用Instead Of触发器，在判断折扣大于0.6时，就中止了更新操作，避免在修改数据之后再回滚操作，减少服务器负担。现将原来的触发器改为Instead Of触发器： 
 
   02  
 
   03CREATE TRIGGER 订单明细_Insert 
 
   04   ON  订单明细 
 
   05   Instead Of INSERT
 
   06AS
 
   07BEGIN
 
   08    SET NOCOUNT ON; 
 
   09    declare @订单ID int, @产品ID int,@单价 money, @数量 smallint,@折扣 real
 
   10    set @订单ID = (select 订单ID from inserted) 
 
   11    set @产品ID = (select 产品ID from inserted) 
 
   12    set @单价 = (select 单价 from inserted) 
 
   13    set @数量 = (select 数量 from inserted) 
 
   14    set @折扣 = (select 折扣 from inserted) 
 
   15       if (@折扣)>0.6 
 
   16          raiserror('折扣不能大于0.6',16,1) 
 
   17       else
 
   18          INSERT INTO 订单明细 (订单ID,产品ID,单价,数量,折扣) 
 
   19            VALUES (@订单ID,@产品ID,@单价,@数量,@折扣) 
 
   20END
 
   21GO

张炜的技术博客

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
INSTEAD OF 触发器

Instead Of触发器与After触发器的工作流程是不一样的。After触发器是在SQL Server服务器接到执行SQL语句请求之后，先建立临时的Inserted表和Deleted表，然后实际更改数据，最后才激活触发器的。而Instead Of触发器看起来就简单多了，在SQL Server服务器接到执行SQL语句请求后，先建立临时的Inserted表和Deleted表，然后就触发了Instead Of触发器，至于那个SQL语句是插入数据、更新数据还是删除数据，就一概不管了，把执行权全权
复制链接

扫一扫

专栏目录