49. SQL- 触发器之DML 触发器(2)

最新推荐文章于 2024-06-20 13:14:51 发布

weixin_34187822

最新推荐文章于 2024-06-20 13:14:51 发布

阅读量124

点赞数

文章标签：数据库

原文链接：http://blog.51cto.com/57388/1630416

版权

其它AFTER触发器操作

设置After 触发器的激活顺序

对于同一个操作，如Insert、Update 或Delete来说，可以建立多个After Insert 触发器，如以下DEMO，已经建立了一个名为―orders_Insert‖的触发器，输出一句友好提示“又添加了一种产品”；之后再建立一个After Insert 触发器，作用也是输出一句友好提示，提示内容为：―再一次告诉你，你又添加了一种产品‖。

--1、创建2个触发器

CREATETRIGGER orders_Insert

ON orders

AFTERINSERT

BEGIN

print'又添加了一种产品'

END

CREATETRIGGER orders_Insert1

ON orders

AFTERINSERT

BEGIN

print'再一次告诉你，你又添加了一种产品'

END

--2、插入一条数据

insertinto Orders

values('a02','2011-09-04','cisco','USA','7000')

--返回：

从上可以看出，一条insert into 语句，触发了2 个触发器

当同一个操作定义的触发器越来越多的时候，触发器被激活的次序就会变得越来越重要了。在SQLServer 2008 里，用存储过程【sp_settriggerorder】可以为每一个操作各指定一个最先执行的After 触发器和最后执行的After 触发器。sp_settriggerorder 语法如下：

sp_settriggerorder[ @triggername = ] '[triggerschema. ] triggername'

, [ @order = ] 'value'

, [ @stmttype = ] 'statement_type'

[, [ @namespace = ] { 'DATABASE'| 'SERVER' |NULL } ]

翻译成中文就是：

sp_settriggerorder触发器名,

激活次序,

激活触发器的动作

参数：

触发器名，要用单引号括起来，因为它是一个字符串。

激活次序可以为First、Last和None：First 是指第一个要激活的触发器；Last 是指它最后一个要激活的触发器；None 是不指激活序，由程序任意触发。

激活触发器的动作可以是：Insert、Update 和Delete。

上面的例子里，先激活的是【orders_Insert】触发器，后激活的是【orders_Insert1】

触发器。如果把【orders_Insert1】触发器设为First 触发器，把【orders_Insert】触发器设为Last 触发器，那么结果将会完全不一样。设置语句如下：

Exec sp_settriggerorder

'orders_Insert1','First','Insert'

Exec sp_settriggerorder

'orders_Insert','Last','Insert'

--插入一条新的数据

insertinto Orders

values('a03','2011-09-04','MII','USA','7000')

触发器返回结果：

在设置After 触发器激活顺序时，还有几点是需要注意的：

每个操作最多只能设一个First触发器和一个Last 触发器。

如果要取消已经设好的First 触发器或Last 触发器，只要把它们设为None 触发器即可。

如果用Alter 命令修改过触发器内容后，该触发器会自动变成None 触发器。所以用Alter 命令也可以用来取消已经设好的First 触发器或Last 触发器。

只有After 触发器可以设置激活次序，Instead Of 触发器不可以设置激活次序。

激活触发器的动作必须和触发器内部的激活动作一致。举例说明：After Insert 触发器，只能为Insert操作设置激活次序，不能为Delete 操作设置激活次序。以下的设置是错误的：

Execsp_settriggerorder

'orders_Insert1','First',Update

触发器的嵌套

当一个触发器执行时，能够触活另一个触发器，这种情况就是触发器的嵌套。在SQL Server 2008 里，触发器能够嵌套到32 层。

如果不想对触发器进行嵌套的话，可以通过【允许触发器激活其他触发器】的服务器配置选项来控制。但不管此设置是什么，都可以嵌套Instead Of 触发器。设置触发器嵌套的选项更改方法为：

（1）打开ManagementStudio，在【对象资源管理】中，右击服务器名，并选择【属性】选项。

（2）单击【高级】节点。

（3）在【杂项】里设置【允许触发器激活其他触发器】为True 或False。

--1、创建一个测试表，用于记录操作记录

在DEMO_DB数据库里建一个操作记录表，用来记录所有数据表的操作，无论是对哪个数据表进行了插入、更新或删除，都可以把操作内容和操作时间记录到操作记录表里。下面是建立操作记录表的SQL 语句：

CREATETABLE Operation

(

seq_numint IDENTITY(1,1) NOT NULL, --编号

op_tablevarchar(50) NOT NULL, --操作表名

op_Sentencevarchar(2000) NOT NULL, --操作语句

op_Contentvarchar(2000) NOT NULL, --操作类容

op_timedatetime NOT NULL --操作时间

CONSTRAINTDF_Operation_time DEFAULT (getdate()), --创建一个默认约束

CONSTRAINTPK_Operation PRIMARY KEYCLUSTERED --针对seq_num列做一个主键约束

( seq_num ASC )

WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

)

ON [PRIMARY]

{ 使用 IGNORE_DUP_KEY选项来处理重复值。

在创建或修改唯一索引或约束时，您可以将IGNORE_DUP_KEY选项设置为ON 或 OFF。当索引创建后，此选项指定对操作多行的INSERT 语句中所出现的重复键值错误的响应方式。

当 IGNORE_DUP_KEY 设置为OFF（默认设置）时，如果有一行或多行含有重复键值，则SQLServer 数据库引擎将拒绝语句操作的所有行。当设置为ON 时，只有包含重复键值的行被拒绝后，才能添加非重复键值。

例如，如果一个单条语句向某个具有唯一索引的表中插入20 行，而其中 10 行含有重复键值，则默认情况下全部20 行都将被拒绝。但是，如果索引选项IGNORE_DUP_KEY 设置为ON，则只有10 个重复键值会被拒绝，其他10 个非重复键值将插入到表中。}

--2、创建一个After Insert 触发器，触发器的作用是输入一条语句：“数据库又有记录变动

了”。语句如下：

CREATETRIGGER Operation_Insert

ON Operation

AFTERINSERT

BEGIN

print'数据库又有记录变动了'

END

--3、创建一个测试表type,用于插入数据时，触发操作

createtable type

(type_name nvarchar(15),type_descrnvarchar(max))

--4、创建立一个After Insert 触发器

当在type 表里插入一条记录的时候，该触发器向操作记录表里插入一条记录，而在操作记录表里插入记录时，将会触发操作记录表里的【Operation_Insert】触发器。

CREATETRIGGER type_Insert

ONtype

AFTERINSERT

BEGIN

Declare

@type_namenvarchar(15), --定义变量@type_name

@type_descrnvarchar(max) --定义变量@type_descr

set @type_name= (Selecttype_name from inserted)

--为@type_name附值为inserted表中type_name

set @type_descr= (Select type_descr from inserted)

--为@type_descr附值为inserted表中type_descr

INSERTINTO Operation(op_table,op_Sentence,op_Content)

–-触发触发器时，将操作内容插入到操作记录表

VALUES('type','insert','type_name：'+@type_name+',type_descr：'+@type_descr)

END

--4、在type 表中插入一条记录

insertinto type

values('book','cisco_bsci')

数据库又有记录变动了

(1 行受影响)

在【消息】对话框可以看到―数据库又有记录变动了‖，这说明，触发器[Operation_Insert]

已经被嵌套激活了

--5、查看表operation 内容

select* from type

select* from Operation

返回结果：

--6、如果把【允许触发器激活其他触发器】的选项设为False，再看看运行结果：

insertinto type

values('book','cisco_ts')

(1 行受影响)

可以看到并未触发嵌套触发器[Operation_Insert]

select* from type

select* from Operation

触发器的递归

触发器的递归是指，一个触发器从其内部又一次激活该触发器。例如一个Insert 触发器的内部还有一条对本数据表插入记录的SQL 语句，那么这个插入语句就有可能再一次激活这个触发器本身。当然，这种递归的触发器内部还会有判断语句，要一定的情况下才会执行那个SQL 语句，否则的话，就会变成死循环了。

上面的例子说的是直接递归的触发器，还有一种是间接递归的触发器，举例说明：当向A 表插入一条记录时，激活了A 表的Insert 触发器，A表的Insert 触发器里有一个SQL语句是对B 表进行Insert 操作的，而在 B 表的Insert触发器里也有一句话是对A 表进行Insert 操作的。这样就是触发器的间接递归。

一般情况来说，SQL Server 服务器是不允许递归的，如果要打开触发器递归的功能，同样是将【允许触发器激活其他触发器】设为True。

设计Instead Of 触发器

InsteadOf 触发器与After 触发器的工作流程是不一样的。After 触发器是在SQL

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

Instead Of 触发器的使用范围

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

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

有可能要回滚修改的SQL语句：例在订单明细表里，折扣字段不能大于0.6，如果插入记录时，折扣大于0.6 的话，回滚操作。如果用Instead Of 触发器，在判断折扣大于0.6 时，就中止了更新操作，避免在修改数据之后再回滚操作，减少服务器负担。

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

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

创建Instead of 触发器

（1）、语法：

CREATETRIGGER 触发器名

ON 数据表名或视图名

InsteadOf INSERT或DELETE或UPDATE

BEGIN

--这里是要运行的SQL语句

END

从上面可以看得出，InsteadOf 触发器与After 触发器的语法几乎一致，只是简单地把After 改为Instead Of。

DEMO：使用InsteadOf 触发器，在判断表orders 价格大于10000时，就中止了更新操作，避免在修改数据之后再回滚操作，减少服务器负担。现将原来的触发器改为InsteadOf 触发器：

--1、使用AFTER 创建触发器：

CREATETRIGGER orders_Insert

ON orders

AFTERINSERT

BEGIN

if (Select ratefrom inserted)>8000

begin

print'rate Not more than 8000'

RollbackTransaction

end

END

--插入一条语句，其中rate>10000，查看返回结果

insertinto Orders

values('a06','2011-09-17','cisco','usa','8500')

--返回：

rateNot more than 8000

消息3609，级别16，状态1，第1 行

事务在触发器中结束。批处理已中止。

--2、使用Instead Of 创建触发器：

CREATETRIGGER orders_Insert

ON orders

InsteadOf INSERT

BEGIN

SETNOCOUNT ON;

declare

@docnochar(20),

@docdatedatetime,

@custnvarchar(200),

@carrencychar(3),

@ratenumeric(5)

set @docno = (selectdocno from inserted)

set @docdate = (selectdocdate from inserted)

set @cust = (selectcust from inserted)

set @carrency = (selectcarrency from inserted)

set @rate = (selectrate from inserted)

if (@rate)>1000

print'rate Not more than 1000'

end

--插入一条语句，其中rate>1000，查看返回结果

insertinto Orders

values('a07','2011-09-18','cisco','usa','5000')

--返回：

rateNot more than 1000

(1 行受影响)

管理DML 触发器

查看DML触发器

使用Management Studio 查看

打开ManagementStudio →数据库 → 表 → 触发器 →

双击要查看的触发器名，Management Studio 自动弹出一个

【查询编辑器】对话框，对话框里显示的是该触发器的内容

使用系统存储过程查看触发器

SP_HELP

SP_HELPTEXT

DEMO:

sp_helporders_insert1

sp_helptextorders_insert1

修改触发器

语法：

ALTERTRIGGER 触发器名

ON 数据表名或视图名

AFTERINSERT或DELETE或UPDATE

BEGIN

--这里是要运行的SQL语句

END

重命名触发器：

sp_rename'旧触发器名','新触发器名'

D、删除触发器：

删除：

droptrigger '触发器名'

禁用：

Altertable 数据表名

Disable或Enable trigger 触发器名或ALL

用Disable可以禁用触发器，用Enable 可以启用触发器；如果要禁用或启用所有触发器，用―ALL‖来代替触发器名

转载于:https://blog.51cto.com/57388/1630416

weixin_34187822

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
49. SQL- 触发器之DML 触发器(2)

其它AFTER触发器操作设置After 触发器的激活顺序对于同一个操作，如Insert、Update 或Delete来说，可以建立多个After Insert 触发器，如以下DEMO，已经建立了一个名为―orders_Insert‖的触发器，输出一句友好提示“又添加了一种产品”；之后再建立一个After Insert 触发器，作用也是输出一句友好提示，提示内容为：―再一次告诉...
复制链接

扫一扫