oracle 插入数据不成功报错

[weixin_41400530]

在公司做数据库维护，需要在某表插入一批数据，大部分都插入成功，却有小部分无法插入并报错，求大神解答


以下是部分插入语句

insert into td_trunk_in(SWITCH_CODE,TRUNKGROUP,TRUNK_TYPE,START_DATE,END_DATE,TRUNKGROUP_CONVERT) values ('H01','4904','2H','20190122','29991231','B_PD_SHGS91_1');
insert into td_trunk_in(SWITCH_CODE,TRUNKGROUP,TRUNK_TYPE,START_DATE,END_DATE,TRUNKGROUP_CONVERT) values ('H01','4905','2H','20190122','29991231','B_PD_SHGS91_2');
insert into td_trunk_in(SWITCH_CODE,TRUNKGROUP,TRUNK_TYPE,START_DATE,END_DATE,TRUNKGROUP_CONVERT) values ('H01','4906','2H','20190122','29991231','B_QZ_SHGS91_1');
insert into td_trunk_in(SWITCH_CODE,TRUNKGROUP,TRUNK_TYPE,START_DATE,END_DATE,TRUNKGROUP_CONVERT) values ('H01','4907','2H','20190122','29991231','B_QZ_SHGS91_2');
insert into td_trunk_in(SWITCH_CODE,TRUNKGROUP,TRUNK_TYPE,START_DATE,END_DATE,TRUNKGROUP_CONVERT) values ('H01','4908','2H','20190122','29991231','B_PD_SHGS92_1');

首先表的键肯定没有违反 绝对没有重复数据



以下是报错涉及的触发器

CREATE OR REPLACE TRIGGER "TIB_TD_TRUNK_IN"

  BEFORE insert ON SETTLE.TD_TRUNK_IN
  FOR EACH ROW
DECLARE
  V_SWITCH_CODE        SETTLE.TD_TRUNK_IN.SWITCH_CODE%type;
  V_TRUNKGROUP	       SETTLE.TD_TRUNK_IN.TRUNKGROUP%type;
  V_TRUNK_TYPE	       SETTLE.TD_TRUNK_IN.TRUNK_TYPE%type;
  V_START_DATE	       SETTLE.TD_TRUNK_IN.START_DATE%type;
  V_END_DATE	       SETTLE.TD_TRUNK_IN.END_DATE%type;
  V_TRUNKGROUP_CONVERT SETTLE.TD_TRUNK_IN.TRUNKGROUP_CONVERT%type;

BEGIN
  V_SWITCH_CODE        := :NEW.SWITCH_CODE;
  V_TRUNKGROUP	       := :NEW.TRUNKGROUP;
  V_TRUNK_TYPE	       := :NEW.TRUNK_TYPE;
  V_START_DATE	       := :NEW.START_DATE;
  V_END_DATE	       := :NEW.END_DATE;
  V_TRUNKGROUP_CONVERT := :NEW.TRUNKGROUP_CONVERT;

  IF (:NEW.SWITCH_CODE = 'H01') THEN
    UPDATE SETTLE.TD_TRUNK_IN
       SET END_DATE = V_START_DATE
     WHERE SWITCH_CODE IN ('H01', 'H02')
       and TRUNKGROUP = V_TRUNKGROUP
       and TRUNK_TYPE = V_TRUNK_TYPE;

    INSERT INTO SETTLE.TD_TRUNK_IN
      (SWITCH_CODE,
       TRUNKGROUP,
       TRUNK_TYPE,
       START_DATE,
       END_DATE,
       TRUNKGROUP_CONVERT)
    VALUES
      ('H02',
       V_TRUNKGROUP,
       V_TRUNK_TYPE,
       V_START_DATE,
       V_END_DATE,
       V_TRUNKGROUP_CONVERT);

    UPDATE SETTLE.TD_TRUNK_OUT
       SET END_DATE = V_START_DATE
     WHERE SWITCH_CODE IN ('H01', 'H02')
       and TRUNKGROUP = V_TRUNKGROUP
       and TRUNK_TYPE = V_TRUNK_TYPE;

    INSERT INTO SETTLE.TD_TRUNK_OUT
      (SWITCH_CODE,
       TRUNKGROUP,
       TRUNK_TYPE,
       START_DATE,
       END_DATE,
       TRUNKGROUP_CONVERT)
    VALUES
      ('H01',
       V_TRUNKGROUP,
       V_TRUNK_TYPE,
       V_START_DATE,
       V_END_DATE,
       V_TRUNKGROUP_CONVERT);

    INSERT INTO SETTLE.TD_TRUNK_OUT
      (SWITCH_CODE,
       TRUNKGROUP,
       TRUNK_TYPE,
       START_DATE,
       END_DATE,
       TRUNKGROUP_CONVERT)
    VALUES
      ('H02',
       V_TRUNKGROUP,
       V_TRUNK_TYPE,
       V_START_DATE,
       V_END_DATE,
       V_TRUNKGROUP_CONVERT);
  END IF;

  IF (V_SWITCH_CODE = 'H03') THEN
    UPDATE SETTLE.TD_TRUNK_IN
       SET END_DATE = V_START_DATE
     WHERE SWITCH_CODE IN ('H03', 'H04')
       and TRUNKGROUP = V_TRUNKGROUP
       and TRUNK_TYPE = V_TRUNK_TYPE;

    INSERT INTO SETTLE.TD_TRUNK_IN
      (SWITCH_CODE,
       TRUNKGROUP,
       TRUNK_TYPE,
       START_DATE,
       END_DATE,
       TRUNKGROUP_CONVERT)
    VALUES
      ('H04',
       V_TRUNKGROUP,
       V_TRUNK_TYPE,
       V_START_DATE,
       V_END_DATE,
       V_TRUNKGROUP_CONVERT);

    UPDATE SETTLE.TD_TRUNK_OUT
       SET END_DATE = V_START_DATE
     WHERE SWITCH_CODE IN ('H03', 'H04')
       and TRUNKGROUP = V_TRUNKGROUP
       and TRUNK_TYPE = V_TRUNK_TYPE;

    INSERT INTO SETTLE.TD_TRUNK_OUT
      (SWITCH_CODE,
       TRUNKGROUP,
       TRUNK_TYPE,
       START_DATE,
       END_DATE,
       TRUNKGROUP_CONVERT)
    VALUES
      ('H03',
       V_TRUNKGROUP,
       V_TRUNK_TYPE,
       V_START_DATE,
       V_END_DATE,
       V_TRUNKGROUP_CONVERT);

    INSERT INTO SETTLE.TD_TRUNK_OUT
      (SWITCH_CODE,
       TRUNKGROUP,
       TRUNK_TYPE,
       START_DATE,
       END_DATE,
       TRUNKGROUP_CONVERT)
    VALUES
      ('H04',
       V_TRUNKGROUP,
       V_TRUNK_TYPE,
       V_START_DATE,
       V_END_DATE,
       V_TRUNKGROUP_CONVERT);
  END IF;

END;

[minsic78]

来个10046吧，能看的清楚点。

[卖水果的net]

首先表的键肯定没有违反 绝对没有重复数据 如果系统违反了唯一约束，那肯定是违反了。 你先把这个 PK 禁用，再执行你这些 insert 语句，执行完成后，你再查询一下，是不是有重复值。

[weixin_41400530]

引用 1 楼 卖水果的net 的回复:

首先表的键肯定没有违反 绝对没有重复数据 如果系统违反了唯一约束，那肯定是违反了。 你先把这个 PK 禁用，再执行你这些 insert 语句，执行完成后，你再查询一下，是不是有重复值。

我执行了 ALTER TABLE td_trunk_in disable constraint PK_TD_TRUNK_IN; 禁用后 insert语句报错还是那个窗口