首页 » 技术乱弹 » 正文

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

发表于: 青年博客 · 2017-7-14 ·  20 views  ·  0 replies 
这个问题发生在SQL Server 2008中,所以我在这里也就以SQL Server 2008 为例子来说了。
前期要实现的需求是这样的,在SQL Server中如果向一张表中插入新的数据,则需要将该数据同步到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表中对应的数据。
额,我又废话了一大堆。好吧,先看下我这个触发器是这样写的:
为了测试以上触发器是否OK,我执行了下面的SQL语句:
执行成功。然后到T_USER表 和 T_USER_BAK表中去看了,数据都已经有了。自己又写了另外一条SQL进行测试:
执行OK。 执行信息如下:
正当自己高兴的时候,却发现了一个诡异的现象。经过对表中数据的查看,我发现,在T_USER表中,三条数据插入时OK的,然T_USER_BAK表中却只有一条数据,这条数据是本次批量插入的第一条数据。
从表面上看,只有 ID为1的这条数据响应触发器了,其他的均没有 就直接插入新数据了。难道是触发器不能处理批量插入么?
最后自己也在网上找了一些资料,发现很多人也遇到该问题。在网上也看到一个比较靠谱的说法:触发器的次数并不是受影响的行数,Inserted可以包含多条记录。
也就是说,我执行了一次SQL批量插入,触发器只会被触发一次。而 Inserted 中包含了我本次插入的所有数据,我没有在触发器内部遍历而是直接插入,经过实验得知,在触发器执行插入动作时,默认是将第一条数据插入到制定的表中的。
当然,在网上也找到了解决方法,那就是利用游标循环,一行一行的读取数据,然后再一行一行的插入数据。根据网上提供的方法,我将触发器再次修改了一下:
这样就解决刚刚上面再对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触发器语法
参数说明:
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 表中的所有值,请使用:
禁用触发器:disable  trigger  触发器名  ON  表名
恢复启用:ENABLE  TRIGGER 触发器名  ON  表名
删除触发器:DROP TRIGGER  触发器名
三、触发器的修改、删除和显示
1.修改触发器 
利用T-SQL修改触发器的语法格式:
语法注释:
修改触发器只需在创建触发器的语法格式里,将create 改为alter
2.删除触发器
利用T-SQL删除触发器的语法格式:
语法注释:
触发器名    要删除的触发器名称 [ ,…n ]   表示可以删除多个触发器
本文链接: http://www.baikeyang.com/code/97228.html
0 like+
«上一篇: :下一篇»

相关文章

部署在多个tomcat同一个项目在同一个浏览器中导致session失效问题

最近在服务器部署环境时,发现了一个现象。同一个项目,部...

GPS纠偏算法

在网上找了一个GPS的纠偏算法,适用于 Google(国内)、高...

Oracle 字段类型clob 修改为varchar2 的方法

在Oracle数据库中,将字段的Clob类型修改为VARCHAR2的方法...

Spring boot 之 Web应用开发篇

本篇文章主要是围绕如何在Spring Boot中进行Web开发呢? 一...

PL/SQL Developer连接数据库提示ORA-12638

使用PL/SQL Developer连接数据库时,提示 “ORA-12638:身份...

WebSocket 简单示例

一、WebSocket简介 WebSocket API是下一代客户端-服务器的...

《批量insert时触发器只响应一条数据的问题》有0个想法

  1. 还没有任何评论,你来说两句吧

Leave a reply