记首次尝试SQL Server触发器

简述

由于业务需求，常常需要在数据库中某些表发生变化时，进行一些操作，如记录日志以备份或后期进行数据追溯、同步不同数据库表的某些状态和字段等。在拿到这些需求时，往往也会第一时间考虑通过书写逻辑层业务代码进行。这种做法比较通用，但往往效率较低，并且不便于维护和更新，因此考虑使用数据库的触发器。本文记录了第一次尝试书写SQL Server的触发器的经验。

初始化

为了测试，首先建立一个用于存储数据的测试表（TriggerTestTable），以及一个用于测试触发器的日志表（LogTable），结构和数据如下：

CREATE DATABASE mydb
USE mydb
GO

CREATE TABLE [dbo].[TriggerTestTable] (
    [id] int NOT NULL ,
    [Val] varchar(20) NULL ,
    [OtherVal] int NULL ,
    PRIMARY KEY ([id])
)
GO

CREATE TABLE [dbo].[LogTable] (
    [id] varchar(30) NOT NULL ,
    [ChangedTableName] varchar(255) NULL ,
    [ChangeLog] varchar(255) NULL ,
    PRIMARY KEY ([id])
)
GO

--------------- 添加初始数据 ---------------
INSERT INTO TriggerTestTable(id, Val, OtherVal) VALUES (1, '1', 11);
INSERT INTO TriggerTestTable(id, Val, OtherVal) VALUES (2, '2', 12);
INSERT INTO TriggerTestTable(id, Val, OtherVal) VALUES (3, '3', 13);
INSERT INTO TriggerTestTable(id, Val, OtherVal) VALUES (4, '4', 14);
INSERT INTO TriggerTestTable(id, Val, OtherVal) VALUES (5, '5', 15);
GO

初始化后LogTable没有数据，TriggerTestTable数据如下：

id	Val	OtherVal
1	1	11
2	2	12
3	3	13
4	4	14
5	5	15

触发器书写

参考网上的一篇博客，浅谈 Sql Server 触发器，尝试进行触发器的书写。发现触发器本质上其实就是一种存储过程，涉及到“增、删、改”操作中的临时表“inserted”和“deleted”和与业务相关的表。

在阅读该博客中的“update 触发器”部分时突然想到，如果Update了数条数据，是否能够保证每条数据都能经过触发器。于是简单尝试了如下触发器，目的在Update测试表TriggerTestTable中Val的时候将修改前后的信息记录进日志表LogTable中。

--------------- 为TriggerTestTable表创建Update触发器 ---------------
CREATE TRIGGER [dbo].[trig_updateValue1]
ON [dbo].[TriggerTestTable]
AFTER UPDATE
AS
BEGIN
    DECLARE @oldVal varchar(20),
            @newVal varchar(20),
            @id INT;
    
    SELECT @oldVal = Val, @id = id FROM deleted;
    SELECT @newVal = Val FROM inserted;

    INSERT INTO LogTable(id, ChangedTableName, ChangeLog) 
    VALUES (
    	convert(VARCHAR, @id),
    	'TriggerTestTable',
    	'OldValue: ' + @oldVal + '; NewValue: ' + @newVal
    );
END
GO

--------------- 执行批量修改语句 ---------------
UPDATE TriggerTestTable
SET Val = '99'
WHERE id > 2;
GO

在最后执行批量更新语句后，查看TriggerTestTable表发现更新语句成功执行，id大于2的Val均修改为99：

id	Val	OtherVal
1	1	11
2	2	12
3	99	13
4	99	14
5	99	15

但LogTable中仅有一条记录：

id	ChangedTableName	ChangeLog
3	TriggerTestTable	OldValue: 3; NewValue: 99

由此推断，批量更新的语句仅仅触发了一次触发器。批量更新时原记录全部加入到“deleted”临时表中，新记录全部加入到“inserted”临时表中，但最终触发器中的语句却仅仅执行了一次。为了验证临时表中的数据量，又书写了如下触发器：

--------------- 禁用旧的TriggerTestTable表的触发器 ---------------
ALTER TABLE [dbo].[TriggerTestTable] 
DISABLE TRIGGER [dbo].[trig_updateValue1]
GO

--------------- 为TriggerTestTable表创建Update触发器 ---------------
CREATE TRIGGER [dbo].[trig_updateValue2]
ON [dbo].[TriggerTestTable]
AFTER UPDATE
AS
BEGIN
    DECLARE @oldCount INT,
            @newCount INT,
            @id INT;
    
    SELECT @oldCount = count(*) FROM deleted;
    SELECT @newCount = count(*) FROM inserted;
    
    INSERT INTO LogTable(id, ChangedTableName, ChangeLog) 
    VALUES (
    	'99', 
    	'TriggerTestTable',
    	'DeleteCount: ' + convert(VARCHAR, @oldCount) + '; InsertCount: ' + convert(VARCHAR, @newCount)
    );
END
GO

--------------- 执行批量修改语句 ---------------
UPDATE TriggerTestTable
SET Val = '88'
WHERE id > 2;
GO

执行批量更新语句后，TriggerTestTable表中id大于2的Val均修改为88：

id	Val	OtherVal
1	1	11
2	2	12
3	88	13
4	88	14
5	88	15

LogTable中的记录也表明了“inserted”和“deleted”临时表中的数据确实均有3条：

id	ChangedTableName	ChangeLog
3	TriggerTestTable	OldValue: 3; NewValue: 99
99	TriggerTestTable	DeleteCount: 3; InsertCount: 3

批量Update触发器

这时就考虑到两种解决方案：

1. 保证增、删、改每次只影响到一条数据；
2. 在触发器中通过游标遍历临时表对每条修改数据进行操作。

其中第一种方案显然很不通用，于是便还是尝试使用游标遍历临时表，对每一条修改的数据进行对应的操作。由于本人目前对存储过程还不是很了解，故仍然参考博客SqlServer使用For循环结合游标更新数据熟悉了一下SQL Server中的游标。修改Update触发器如下：

--------------- 禁用旧的TriggerTestTable表的触发器 ---------------
ALTER TABLE [dbo].[TriggerTestTable] 
DISABLE TRIGGER [dbo].[trig_updateValue1]

ALTER TABLE [dbo].[TriggerTestTable] 
DISABLE TRIGGER [dbo].[trig_updateValue2]
GO

--------------- 为TriggerTestTable表创建Update触发器 ---------------
CREATE TRIGGER [dbo].[trig_updateValue3]
ON [dbo].[TriggerTestTable]
AFTER UPDATE
AS
BEGIN
    IF update(Val)  -- 仅在Val列变化时才执行
    BEGIN
        DECLARE @oldVal varchar(20),
                @newVal varchar(20),
                @id INT;

        DECLARE ID_Cursor CURSOR --定义游标
        FOR (SELECT id FROM deleted) --查出需要的集合放到游标中

        OPEN ID_Cursor; --打开游标
        FETCH NEXT FROM ID_Cursor INTO @id;
        WHILE @@FETCH_STATUS = 0  --判断游标是否为读取失败，读取失败则为-1，代表到达最后一行
            BEGIN
                SELECT @oldVal = Val FROM deleted WHERE id = @id;
                SELECT @newVal = Val FROM inserted WHERE id = @id;

                INSERT INTO sync_log(id,tableName,cols) 
                VALUES (
                	convert(VARCHAR, @id + 100),
                	'TriggerTestTable',
                	'OldValue: ' + @oldVal + '; NewValue: ' + @newVal
                );

                FETCH NEXT FROM ID_Cursor INTO @id;
            END
        CLOSE ID_Cursor; --关闭游标
        DEALLOCATE ID_Cursor; --释放游标
    END
END
GO

--------------- 执行批量修改语句 ---------------
UPDATE TriggerTestTable
SET Val = '66'
WHERE id > 2;
GO

批量更新语句成功执行，TriggerTestTable表中id大于2的Val均修改为66：

id	Val	OtherVal
1	1	11
2	2	12
3	66	13
4	66	14
5	66	15

LogTable中也新增了3条记录，说明触发器成功遍历“inserted”和“deleted”临时表，并进行了对应的操作，每条数据的修改都得到了记录：

id	ChangedTableName	ChangeLog
3	TriggerTestTable	OldValue: 3; NewValue: 99
99	TriggerTestTable	DeleteCount: 3; InsertCount: 3
103	TriggerTestTable	OldValue: 88; NewValue: 66
104	TriggerTestTable	OldValue: 88; NewValue: 66
105	TriggerTestTable	OldValue: 88; NewValue: 66

之后为了测试update()函数，又批量修改了OtherVal字段：

--------------- 执行批量修改语句 ---------------
UPDATE TriggerTestTable
SET OtherVal = 99
WHERE id < 3;
GO

批量更新语句成功执行，TriggerTestTable表中id小于3的OtherVal均修改为99：

id	Val	OtherVal
1	1	99
2	2	99
3	66	13
4	66	14
5	66	15

但由于添加了IF update(Val)语句，在修改OtherVal字段时虽然进入了触发器，但Val字段没有变化，故并未执行触发器中的具体操作，所以LogTable并没有任何变化。