杂谈–DML触发器学习

触发器可以理解为由特定事件触发的存储过程,
和存储过程、函数一样,触发器也支持CLR,目前SQL
Server共支持以下几种触发器:

14第十四章触发器

触发器按类型分为三类:

  1. DML触发器, 表/视图级有效,可由DML语句 (INSERT, UPDATE, DELETE)
    触发;

  2. DDL 触发器,数据库级有效,可由DDL语句 (CREATE, ALTER, DROP 等) 触发;

  3. LOGON 触发器, 实例级有效,可由用户账号登录(LOGON)数据库实例时触发;

 

  1. DML 触发器,在数据变更时触发;

  2. DDL 触发器,在修改数据库级别或实例级别对象时触发;

  3. Login 触发器,在用户登录时触发;

 

DML –>
AFTER / FOR   UPDATE , INSERT , DELETE  — 用来级联删除

 

一. DML触发器

       
 –> INSTEAD OF  在 时间之前触发,相当于 bef

最常见的是DML触发器,DML触发器又可以分为两类: INSTEAD
OF触发器和AFTER触发器(部分书上有提到FOR触发器,其实就是AFTER
触发器,只是写法不同而已)。

1. 语句级触发器/行级触发器

INSERTED,
DELETED 两张表要好好利用。

从功能来看,INSTEAD
OF触发器用来替换实际的数据修改操作,而AFTER触发器用来在实际操作完成后进行后续操作。例如对于DELETE操作,如果我们期望只修改数据状态来标示数据已被删除而不是将数据从表中删除,那么我们可以使用INSTEAD
OF触发器来实现;如果我们期望在删除数据后在其他表记录删除操作的发生时间,那么我们可以使用AFTER触发器来实现。

在SQL
Server中,从定义来说只有语句级触发器,但如果有行级的逻辑要处理,有两个仅在触发器内有效的表
(inserted, deleted),
存放着受影响的行,可以从这两个表里取出特定的行并自行定义脚本处理;

在创建 DML
触发器时,不能使用下列语句:

从执行来看,INSTEAD OF触发器和AFTER触发器的所处的执行时期不同,SQL
Server中的触发顺序为:

在ORACLE中,
对表做一次DML操作产生一次触发,叫语句级触发器,另外还可以通过指定[FOR
EACH
ROW]子句,对于表中受影响的每行数据均触发,叫行级触发器,原有行用:OLD表示,新行用:NEW表示;

CREATE /
ALTER /DROP DATABASE

  1. 触发INSTEAD OF触发器

  2. 触发DEFAULT 约束

  3. 触发主键/唯一/CHECK约束

  4. 触发外键约束

  5. 触发AFTER 触发器

 

LOAD DATABASE
/ LOAD LOG / RECONFIGURE

因此如果期望修改操作顺利执行而不触发约束导致回滚的话,可以使用INSTEAD
OF触发器来将实现(在INSTEAD OF 触发器中修改使数据满足约束条件)。

2. BEFORE/AFTER/INSTEAD OF

RESTORE
DATABASE  / RESTORE LOG

 

在SQL Server中,从定义来说只有AFTER/INSTEAD
OF触发器,在表上支持AFTER触发器,在表/视图上支持INSTEAD
OF触发器,对于BEFORE触发器的需求可以尝试通过INSEAD OF触发器来实现;

自动事务处理模式下,还是在隐式或显示事务处理模式下,只要在
触发器中发出 BEGIN TRANSACTION
语句,实际上就开始了一个嵌套事务,当触发器中使用 ROLLBACK TRANSACTION
语句回滚嵌套事务时,触发器本身发出的所有的 BEGIN TRANSACTION
语句回滚嵌套事务时,触发器本身发出的额所有 BEGIN TRANSACTION
语句豆浆被忽略, ROLLBACK 将回滚到最外部的 BEGIN TRANSACTION 。而在 这
最外部的 之前的 事务都已经提交的就不会收到影响,

因为INSTEAD OF
触发器改写了实际要发生的修改操作,因此每个表上每种修改类型(DELETE/INSERT/UPDATE)只能有一个INSTEAD
OF 触发器;而AFTER 触发器没有类似限制,可以创建多个AFTER触发器。

SQL Server DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

而这个 回滚操作也会终止 批处理中 对 该语句后面语句的执行。

问题来了,在存在多个AFTER触发器情况下,AFTER触发器按什么顺序来执行呢?SQL
Server允许针对每种修改类型(DELETE/INSERT/UPDATE)指定一个最先触发和最后触发的AFTER触发器,但不能控制其余的触发器触发顺序。

在ORACLE中,在表上支持BEFORE/AFTER触发器,在视图上支持INSTEAD
OF触发器,比如ORACLE中无法直接对视图做DML操作,可以通过INSTEAD
OF触发器来变样完成;

因此,若要在 触发器中进行部分回滚,应当使用 SAVE TRANSACTION
语句设置一个事务保存点,这样就不会回滚到 外部的 事务中去了。

指定最先执行的AFTER触发器:

ORACLE DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

注意:  在 触发器中 书写 COMMIT TRANSACTION 的 语句,如果之前有 BEGIN
TRANSACTION 语句,会被认为是

--指定针对INSERT操作最先触发的AFTER触发器
EXEC sys.sp_settriggerorder
@triggername='tr_TB1_INSERT',
@order='First',
@stmttype='INSERT'

 

仅提交该嵌套事务,如果 在 commit 之后仍然有 ROLLBACK TRANSACTION
那么仍然会回滚到最外部的 事务。

 

3. 触发条件

if (update(name)) 

说完触发顺序,再来说道说道触发次数,装逼的说法为:DML trrigers have
statement scope and only fire just once regardless of how many rows
affected.通俗说法就是对于一条语句,不管语句修改了多少行(0行或者1000行),对应该操作类型的触发器都会被触发并且只触发一次。

(1) 不能触发的情况

 

PS:上面说的Fire only
once只是针对执行的SQL语句,并不包含该触发器内部的SQL语句

对于UPDATE,DELETE操作而言,均会触发触发器;而对于INSERT或者说IMPORT的情况,是可以控制不去触发的。

用来判断 更新的是哪列, COLUMNS_UPDATED() 测试多个列,
但这个列 是 按字节  加起来算的,这个函数返回一个

 

  • 大批量导入操作,如:BULK INSERT, bcp/INSERT… SELECT * FROM
    OPENROWSET,都有FIRE_TRIGGERS/IGNORE_TRIGGERS选项,可以设置是否触发触发器;
  • 导入导出向导/SSIS,如果目标是表,也有FIRE_TRIGGERS的设置选项;
  • 另外truncate操作也不会触发;

或多个从左至右排序的字节。 P346 是一个很经典的应用。

SQL
server中有两种特殊的触发器:嵌套(Nested)触发器和递归(Recursive)触发器,由Demo来解释下:

(2) 嵌套触发器 (Nested Triggers), 循环/递归触发器 (Recursive
Triggers)

指定 FIRST
触发器 和 LAST 触发器

嵌套(Nested)触发器:在TB1和TB2上创建触发器,当TB1上TR_TB1_INSERT1被触发时,TR_TB1_INSERT1中的语句执行导致TB2上TR_TB2_INSERT1被触发

嵌套触发器,就是一次操作触发了一个触发器,然后触发器里的语句继续触发其他触发器,如果继续回头触发了自己,那么就是递归触发器。

FIRST 和 LAST
触发器之间的执行并没有先后顺序:

--================================
--在TB1和TB2上创建触发器,当TB1上TR_TB1_INSERT1被
--触发时,TR_TB1_INSERT1中的语句执行导致TB2上
--TR_TB2_INSERT1被触发,即属于Nested触发器
CREATE TRIGGER TR_TB1_INSERT1
ON dbo.TB1
AFTER INSERT
AS
BEGIN
INSERT INTO TB2(C1)
SELECT C1 FROM inserted

END
GO

CREATE TRIGGER TR_TB2_INSERT1
ON dbo.TB2
AFTER INSERT
AS
BEGIN

SELECT 1

END

对于AFTER触发器有个两个开关分别控制嵌套触发和递归触发:

sp_settriggerorder 

 

exec sp_configure 'nested triggers'

@triggername =’ud_trig/ins_trig/del_trig’, @order = ‘first/last’,
@stmttyp = ‘update / insert / delete’;

递归(Recursive)触发器可分为直接递归(Directed
Recursive)触发器和间接递归(Indirect Recursive)触发器

这个参数默认值为1,
也就是说允许AFTER触发器嵌套,最多嵌套32层,设为0就是不允许AFTER触发器嵌套,如下:

由于 INSTEAD OF 触发器一直在对基础表进行更新前激发,因此不能讲 INSTEAD
OF 触发器指定为 第一或 最后 一个触发器

直接递归(Directed Recursive)触发器:

exec sp_configure 'nested triggers',0
RECONFIGURE

如果使用了 ALTER TRIGGER 语句 更改了 First 或 Last
触发器,则会删除它们的顺序值,必须使用 sp_settriggerorder 来重新设置。

在TB1创建触发器,当TB1上TR_TB1_INSERT1被触发时,TR_TB1_INSERT1中的语句执行导致TB1上TR_TB1_INSERT1再次被触发

但这个参数有两个另外:

可以通过 OBJECTPROPERTY()函数的ExecIsFirstDeleteTrigger ,
ExecIsFirstInsertTrigger,ExecIsFirstUPdate….等属性来确定触发器时 First
触发器,还是LAST 触发器。

--================================
--在TB1创建触发器,当TB1上TR_TB1_INSERT1被触发时,
--TR_TB1_INSERT1中的语句执行导致TB1上TR_TB1_INSERT1
--再次被触发,即属于直接递归(Directed Recursive)触发器。
ALTER TRIGGER TR_TB1_INSERT1
ON dbo.TB1
AFTER INSERT
AS
BEGIN
--限制递归层数为10层
    IF(@@NESTLEVEL<10)
    BEGIN
        INSERT INTO TB1(C1)
        SELECT C1+1 FROM inserted
    END
END
GO
  • INSTEAD OF触发器,可以嵌套,不受这个参数开关与否影响;
  • AFTER触发器,即使打开该选项,也不会自己嵌套自己(即递归),除非打开了RECURSIVE_TRIGGERS选项,也就是循环/递归触发器;

    –create table, sql server 2016 & higher
    drop table if exists A
    GO
    create table A(id int)
    GO

    –create DML trigger
    drop trigger if exists tri_01
    GO
    create TRIGGER tri_01
    ON A
    AFTER INSERT, UPDATE, DELETE
    as
    begin

    if @@NESTLEVEL = 32
    begin
        return
    end 
    insert A values(0)
    

    end
    GO

    –check nested triggers server option
    exec sp_configure ‘nested triggers’
    –name minimum maximum config_value run_value
    –nested triggers 0 1 1 1

    –test with RECURSIVE_TRIGGERS off
    ALTER DATABASE dba set RECURSIVE_TRIGGERS off
    select is_recursive_triggers_on, from sys.databases
    GO
    insert A values(1)
    select
    from A
    –id
    –1
    –0

    –test with RECURSIVE_TRIGGERS on
    ALTER DATABASE dba set RECURSIVE_TRIGGERS on
    select is_recursive_triggers_on, * from sys.databases
    GO

    truncate table A
    insert A values(1)
    select * from A –32 rows

    –如果没有加@@NESTLEVEL判断并退出,会出现32层限制的报错,并且表里不会插入任何数据
    /*
    Msg 217, Level 16, State 1, Procedure tri_01, Line 10
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    select from A –0 rows/

    –删表会级联删除触发器,就像索引
    drop table A

嵌套和递归触发器

 

 

无论是 DML 触发器还是DDL
触发器,如果出现了一个触发器执行启动另一个触发器的操作都属于嵌套触发器。32层

间接递归(Indirect Recursive)触发器:

循环/递归触发器的前提就是嵌套触发器,只有允许嵌套了才可以递归(递归也就是嵌套并触发自己),递归有直接和间接两种情况:

可以通过nested triggers 服务器配置选项来空值是否可以嵌套AFTER 触发器。
INSTEAD OF 触发器嵌套不受此选项影响。参考下面的语句:

在TB1和TB2上创建触发器,当TB1上TR_TB1_INSERT1被触发时,TR_TB1_INSERT1中的语句执行导致TB2上TR_TB2_INSERT1被触发,而TB2上TR_TB2_INSERT1的触发器执行时又导致TB1上TR_TB1_INSERT1被触发,从而引发循环。

  • 直接递归:就是A表的DML触发器再回来对A表进行DML操作,如上例;
  • 间接递归:就是A表DML触发器去操作B表,然后B表上触发器回来操作A表,如下例;

    –create table, sql server 2016 & higher
    drop table if exists A
    drop table if exists B
    GO
    create table A(id int)
    create table B(id int)
    GO

    –create DML trigger
    drop trigger if exists tri_01
    drop trigger if exists tri_02
    GO
    create TRIGGER tri_01
    ON A
    AFTER INSERT, UPDATE, DELETE
    as
    begin

    if @@NESTLEVEL = 32
    begin
        return
    end 
    insert B values(0)
    

    end
    GO

    create TRIGGER tri_02
    ON B
    AFTER INSERT, UPDATE, DELETE
    as
    begin

    if @@NESTLEVEL = 32
    begin
        return
    end 
    insert A values(0)
    

    end
    GO

    –test with nested triggers server option ON
    exec sp_configure ‘nested triggers’,1
    RECONFIGURE

    –test with RECURSIVE_TRIGGERS off
    ALTER DATABASE dba set RECURSIVE_TRIGGERS off
    select is_recursive_triggers_on, * from sys.databases
    GO

    truncate table A
    truncate table B
    insert A values(1)
    select from A –16 rows
    select
    from B –16 rows

    –test with RECURSIVE_TRIGGERS on
    ALTER DATABASE dba set RECURSIVE_TRIGGERS on
    select is_recursive_triggers_on, * from sys.databases
    GO

    truncate table A
    truncate table B
    insert A values(1)
    select from A –16 rows
    select
    from B –16 rows

    –test with nested triggers server option OFF
    exec sp_configure ‘nested triggers’,0
    RECONFIGURE

    –test with RECURSIVE_TRIGGERS off
    ALTER DATABASE dba set RECURSIVE_TRIGGERS off
    select is_recursive_triggers_on, * from sys.databases
    GO

    truncate table A
    truncate table B
    insert A values(1)
    select from A –1
    select
    from B –0

    –test with RECURSIVE_TRIGGERS on
    ALTER DATABASE dba set RECURSIVE_TRIGGERS on
    select is_recursive_triggers_on, * from sys.databases
    GO

    truncate table A
    truncate table B
    insert A values(1)
    select from A –1
    select
    from B –0

    –删表会级联删除触发器,就像索引
    drop table A, B

  • 可以看出数据库选项RECURSIVE_TRIGGERS,仅对直接递归有效,对间接递归无效;可以通过Nest
    Triggers的开关来控制是否允许嵌套,从而控制是否允许间接递归;

  • 不论直接递归,还是间接递归,递归次数都有32次嵌套的上限;

sp_configure ‘nested triggers’,1 — 设置 为 1 允许 after 触发器嵌套

--================================
--在TB1和TB2上创建触发器,当TB1上TR_TB1_INSERT1被
--触发时,TR_TB1_INSERT1中的语句执行导致TB2上
--TR_TB2_INSERT1被触发,而TB2上TR_TB2_INSERT1的
--触发器执行时又导致TB1上TR_TB1_INSERT1被触发,从而
--引发循环,即间接递归(Indirect Recursive)触发器
CREATE TRIGGER TR_TB1_INSERT1
ON dbo.TB1
AFTER INSERT
AS
BEGIN
    IF(@@NESTLEVEL<10)
    BEGIN
        INSERT INTO TB2(C1)
        SELECT C1 FROM inserted
    END

END
GO

CREATE TRIGGER TR_TB2_INSERT1
ON dbo.TB2
AFTER INSERT
AS
BEGIN
    IF(@@NESTLEVEL<10)
    BEGIN
        INSERT INTO TB1(C1)
        SELECT C1 FROM inserted
    END
END

总结下来:

GO

需要注意的是:

  1. AFTER触发器,默认Nest
    Triggers值为1,即允许触发器嵌套,上限32层,间接递归也是可以的,直接递归需要开启数据库选项RECURSIVE_TRIGGERS;

  2. INSTEAD OF触发器,不受Nest
    Triggers选项影响,均可以嵌套,上限32层,间接递归也是可以的,直接递归无论是否开启数据库选项RECUSIVE_TRIGGERS,都无效;把上面两个脚本示例中的AFTER改为INSTEAD
    OF即可演示。

RECONFIGURE; –使用新环境值

  1. 嵌套(Nested)触发器在sys.configurations中配置,默认开启

 

EXEC sp_configure ‘nested triggers’; –查看 nested triggers 选项设置

2.
(Recursive)触发器在数据库级别配置,默认为关闭,即不允许直接递归(Directed
Recursive)触发器,但不影响间接递归(Indirect
Recursive)触发器,如果需要禁用递归(Indirect
Recursive)触发器,需要同时禁用嵌套(Nested)触发器和(Recursive)触发器

4.
触发器中无法commit/rollback事务

GO

3.
由于嵌套触发器会消耗大量资源(需要保留每层触发器的上下文以便回滚),因此默认限制最多嵌套32层。

--create table, sql server 2016 & higher
drop table if exists A
GO
create table A(id int)
GO

--create DML trigger
drop trigger if exists tri_01
GO
create TRIGGER tri_01
ON A
AFTER INSERT, UPDATE, DELETE 
as
begin
    if @@NESTLEVEL = 32
    begin
        return
    end 
    insert A values(0)
    commit
end
GO

begin tran
insert A values(1)
/*
Msg 3609, Level 16, State 1, Procedure tri_01, Line 10
The transaction ended in the trigger. The batch has been aborted.
*/

递归 P349
 有个经典例子 由于有 update() 函数检测,作为递归终止条件。

 

在SQL
Server和Oracle中都是这样,触发器作为整个事务的一部分存在,但是并不控制整个事务的提交/回滚,为保证数据一致性,事务逻辑由触发器外层的语句来控制。

递归分为 

行版本(Row version)

 

直接递归, 如 应用程序更新 T3 表,从而触发了 触发器 Trig3 , Trig3
再次更新表T3,从而再次出发了触发器Trig3

在SQL Server多中功能中使用到row
version来保留多个版本的数据,这些功能有:

二. DDL触发器

间接递归。 即中间经过另外的表中转还是触发了第一张表的触发器:

  1. MARS

  2. Triggers

  3. Online indexing

  4. Optimistic Transaction Isolation Levels

SQL Server
2005开始支持DDL触发器,它不只限于对CREATE/ALTER/DROP操作有效,支持的DDL事件还有比如:权限的GRANT/DENY/REVOEK,
对象的RENAME, 更新统计信息等等,可通过DMV查看更多支持的事件类型如下:

应用程序更新了 表 T1, 从而触发了触发器Trig1 , Trig1
更新了表T2,从而出发了触发器 Trig2.Trig2转而更新了 表T1 ,
从而再次触发了 Trig1.

因此在使用触发器时,应考虑到可能会为表增加额外14bytes的行版本存储指针

select * from sys.trigger_event_types
where type_name not like '%CREATE%'
  and type_name not like '%ALTER%'
  and type_name not like '%DROP%'

注意: 只有在设置 RECURSIVE_TRIGGERS
数据库选项为 ON 的情况下,才允许以递归方式调用AFTER
触发器:

如下面例子中,表中数据被删除一半,但由于数据只是表示为gost,尚未真正移除,而由于触发器存在,每行额外增加14byte的数据,从而导致页拆分,最终使得删除操作完成后表反而增大。

注意:

ALTER DATABASE
AdventureWorks 

测试代码:

  1. TRUNCATE不在DDL触发器的事件类型中,SQL Server中将Truncate
    归为DML操作语句,虽然它也并不触发DML触发器,就像开启开关的大批量导入操作
    (Bulk Import Operations) 一样;

SET RECURSIVE_TRIGGERS
ON;

USE tempdb
--================================
--创建测试表
DROP TABLE TB1
GO
CREATE TABLE TB1
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    C2 INT NOT NULL,
    C3 VARCHAR(MAX)
)
GO
--================================
--创建Delete触发器
CREATE TRIGGER TR_TB1_DELETE
ON dbo.TB1
AFTER DELETE
AS
BEGIN
RETURN 
END
GO
--================================
--插入5w数据
INSERT INTO TB1(C2)
SELECT TOP(5000) 1 AS C2 FROM sys.all_columns T
GO 10

--================================
--查看表TB1使用的页
DBCC TRACEON(3604)
GO
DBCC IND('tempdb','TB1',1)
GO
--================================
--删除一半的数据
DELETE FROM  dbo.TB1
WHERE ID%2=0

GO
--================================
--查看表TB1使用的页
DBCC TRACEON(3604)
GO
DBCC IND('tempdb','TB1',1)
GO

2.
DDL触发器中捕获的信息都由EVENTDATA()函数返回,返回类型为XML格式,需要用XQuery来读取;

Instead of 触发器:

PS:
如果表中不存在LOB或者VARCHAR(MAX)之类的大字段,不存在ROW_OVERFLOW数据页,则SQL
Server不会为每行增加14byte的行版本存储指针

 

— instead of insert

–==============================================================