批量insert时触发器只响应一条数据的问题

这个问题发生在SQL Server 2008中，所以我在这里也就以SQL Server 2008 为例子来说了。

前期要实现的需求是这样的，在SQL Server中如果向一张表中插入新的数据，则需要将该数据同步到Oracle中制定的表中去。但是，由于利用触发器去实现这个的时候出现了如下异常信息：

[SQL]-- use BGPS;
INSERT INTO T_USER_BAK(ID, NAME, PASSWORD, PHONE) VALUES (1, 'ADMIN', 'ADMIN', '10000')
[Err] 42000 - [SQL Server]无法执行该操作，因为链接服务器 "BEIBEN" 的 OLE DB 访问接口 "OraOLEDB.Oracle" 无法启动分布式事务。
01000 - [SQL Server]链接服务器"BEIBEN"的 OLE DB 访问接口 "OraOLEDB.Oracle" 返回了消息 "新事务不能登记到指定的事务处理器中。 "。

该问题也是让我折腾了整整一天，各种搜索，各种查找资料，结果还是没有找到能解决该问题的办法。因为工作的时间关系，最终还是妥协采用其他方法来替代这种方法来实现了。

在 SQL Server 中创建一张临时表，用来记录对该表的操作日志，然后利用程序去定时扫描这个张日志表，让程序来替代触发器去实现同步的操作。大概就是这个思路。

好了，巴拉巴拉废话了一大推了，现在就来正式说说这个问题吧。

SQL Server 中有我创建了两张表：T_USER 和 T_USER_BAK；表里面的字段都是一样的，有ID，NAME，PASSWORD，PHONE 这四个。

当T_USER有新插入，则触发器自动将本次对该表的数据记录备份到T_USER_BAK；然后就如我上面说的那个思路，程序后台写个定时器不停的去扫描T_USER_BAK表，如果里面有数据，则表示有新增数据，则将该数据插入Oracle中对应的表，然后删除T_USER_BAK表中对应的数据。

额，我又废话了一大堆。好吧，先看下我这个触发器是这样写的：

CREATE TRIGGER [dbo].[USER_Trigger]
ON [dbo].[NewTable]
AFTER INSERT
AS
BEGIN
    DECLARE @id int, @name VARCHAR(50), @password VARCHAR(50), @phone VARCHAR(50) ;
    --在inserted表中查询已经插入记录信息
    SELECT @id = id, @name = name,@password = password,@phone = phone FROM inserted;
    INSERT BGPS.dbo.T_USER_BAK values(@id,@name,@password,@phone);
    print '添加成功！';
END
GO

为了测试以上触发器是否OK，我执行了下面的SQL语句：

DELETE T_USER;
DELETE T_USER_BAK;
INSERT INTO T_USER(ID, NAME, PASSWORD, PHONE) VALUES (1, 'ADMIN', 'ADMIN', '10000');

执行成功。然后到T_USER表和 T_USER_BAK表中去看了，数据都已经有了。自己又写了另外一条SQL进行测试：

DELETE T_USER;
DELETE T_USER_BAK;
INSERT INTO T_USER(ID, NAME, PASSWORD, PHONE) VALUES
(1, 'ADMIN', 'ADMIN', '10000'),
(2, 'LIMING', '123456', '10000'),
(3, 'XIAOHUA', '123456', '10000');

执行OK。执行信息如下：

[SQL]DELETE T_USER
受影响的行: 0
时间: 0.002s
 
[SQL] DELETE T_USER_BAK
受影响的行: 0
时间: 0.001s
 
[SQL]
INSERT INTO T_USER(ID, NAME, PASSWORD, PHONE) VALUES
(1, 'ADMIN', 'ADMIN', '10000'),
(2, 'LIMING', '123456', '10000'),
(3, 'XIAOHUA', '123456', '10000')
添加成功！
 
受影响的行: 3
时间: 0.023s

正当自己高兴的时候，却发现了一个诡异的现象。经过对表中数据的查看，我发现，在T_USER表中，三条数据插入时OK的，然T_USER_BAK表中却只有一条数据，这条数据是本次批量插入的第一条数据。

从表面上看，只有 ID为1的这条数据响应触发器了，其他的均没有就直接插入新数据了。难道是触发器不能处理批量插入么？

最后自己也在网上找了一些资料，发现很多人也遇到该问题。在网上也看到一个比较靠谱的说法：触发器的次数并不是受影响的行数，Inserted可以包含多条记录。

也就是说，我执行了一次SQL批量插入，触发器只会被触发一次。而 Inserted 中包含了我本次插入的所有数据，我没有在触发器内部遍历而是直接插入，经过实验得知，在触发器执行插入动作时，默认是将第一条数据插入到制定的表中的。

当然，在网上也找到了解决方法，那就是利用游标循环，一行一行的读取数据，然后再一行一行的插入数据。根据网上提供的方法，我将触发器再次修改了一下：

CREATE TRIGGER [dbo].[USER_Trigger]
ON [dbo].[NewTable]
AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @RepeatCount int = 0
  DECLARE @id int, @name VARCHAR(50), @password VARCHAR(50), @phone VARCHAR(50);
  DECLARE cur_insert cursor for select id, name, password, phone from inserted;   /*声明游标并指明游标操作的对象*/
  open cur_insert;    /*打开游标*/
  fetch next from cur_insert into @Id,@name,@password,@phone;   /*读取下一行*/
  while @@fetch_status=0
  BEGIN
    print '开始写入！';
    insert BGPS.dbo.T_USER_BAK values(@id,@name,@password,@phone);
    print '开始读取下一行！';
    fetch next from cur_insert into @Id,@name,@password,@phone;   /*读取下一行*/
  END
  /*关闭游标*/
  close cur_insert;
  deallocate cur_insert;
  print '写入完成！';
END
GO

这样就解决刚刚上面再对T_USER批量插入时，触发器只同步一条数据的问题。

自己平时使用触发器也不是太频繁，所以里面的很多坑也不是太了解。但是上面的这个解决方法还是有缺陷的，在插入数据量达到几百万时，这个性能可能就……（你懂的。），除非再在触发器中分批次去处理之类的。看到网上也有很多人吐槽触发器的各种坑。

由于自己平时也不怎么使用触发器，很多知识都忘完了，最后就将触发器的一些知识贴上，以防自己老年健忘的老毛病。

SQL触发器

一、触发器的基本概念
1、触发器的定义
触发器是一种特殊的存储过程，它和表密切相连，可以看作是表格定义的一部分。当用户对指定表操作时，触发器会自动执行。触发器在 update、insert、delete等操作执行结束后才执行。
2、触发器的功能
(1)级联更新数据库中相关表的数据；
(2)实现多表之间数据的一致性；
(3)执行比检查约束更复杂的约束操作；
(4)调用存储过程；
(5)在一张表的update、insert、delete操作上可设置多个触发器。
3、使用触发器应注意的问题
(1)当使用约束、规则、默认值等方法能够实现数据的完整性，就不用触发器实现；T1,T2 INSERT (2)只有表的拥有者才可以在表上创建或删除触发器，这种权限不准转授； (3)使用update语句可以一次对多行数据进行修改，而触发器只被触发一次； (4)触发器只能在当前数据库中创建，触发器的命名必须要遵守标识符的命名规则。
4、触发器的优点
(1)触发器是自动的；
(2)触发器可以通过数据库中的相关表进行层叠更改；
(3)触发器可以强制限制，这些限制比用 check 所定义的约束更复杂
5、触发器的工作原理 inserted表和deleted表的特点如下：
（1）这两张表是逻辑表，由数据库管理，用户不能对他们进行修改；
（2）这两张表存储在内存中，而不是存储在数据库中；
（3）这两张表的结构与被该触发器作用的表结构相同；
（4）当触发器完成操作后，这两张表会自动删除；
（5）两张表中保存的数据是因用户操作而被影响到的原数据或新数据；
（6）这两张表是只读表。

二、触发器的建立
SQL触发器语法

create trigger trigger_name
on {table_name | view_name}
{for | After | Instead of }
[ insert, update,delete ]
as
  sql_statement

参数说明：
trigger_name
是触发器的名称。触发器名称必须符合标识符规则，并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。Table | view
是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。
AFTER
指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。
如果仅指定 FOR 关键字，则 AFTER 是默认设置。
不能在视图上定义 AFTER 触发器。
INSTEAD OF
指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。
在表或视图上，每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。
INSTEAD OF 触发器不能在 WITH CHECK OPTION 的可更新视图上定义。如果向指定了 WITH CHECK OPTION 选项的可更新视图添加 INSTEAD OF 触发器，SQL Server 将产生一个错误。用户必须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。
{ [DELETE] [,] [INSERT] [,] [UPDATE] }
是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。
对于 INSTEAD OF 触发器，不允许在具有 ON DELETE 级联操作引用关系的表上使用 DELETE 选项。同样，也不允许在具有 ON UPDATE 级联操作引用关系的表上使用 UPDATE 选项。
* deleted 和 inserted是逻辑（概念）表。
这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用户操作可能更改的行的旧值或新值。例如，若要检索 deleted 表中的所有值，请使用：

SELECT * FROM deleted

禁用触发器:disable trigger 触发器名 ON 表名

恢复启用：ENABLE TRIGGER 触发器名 ON 表名

删除触发器：DROP TRIGGER 触发器名

三、触发器的修改、删除和显示
1．修改触发器
利用T-SQL修改触发器的语法格式：

alter  trigger 触发器名称
on  表名
for  update[,insert ,delete]
as
   begin
      sql语句
end
return

语法注释：
修改触发器只需在创建触发器的语法格式里，将create 改为alter
2．删除触发器
利用T-SQL删除触发器的语法格式：

drop trigger  触发器名 [ ,...n ]

语法注释：
触发器名要删除的触发器名称 [ ,...n ] 表示可以删除多个触发器

批量insert时触发器只响应一条数据的问题

添加新评论

标签