在公司做資料庫維護，需要在某表插入一批資料，大部分都插入成功，卻有小部分無法插入并報錯，求大神解答
以下是部分插入陳述句
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;
uj5u.com熱心網友回復：
首先表的鍵肯定沒有違反 絕對沒有重復資料如果系統違反了唯一約束，那肯定是違反了。
你先把這個 PK 禁用，再執行你這些 insert 陳述句，執行完成后，你再查詢一下，是不是有重復值。
uj5u.com熱心網友回復：
我執行了 ALTER TABLE td_trunk_in disable constraint PK_TD_TRUNK_IN; 禁用后 insert陳述句報錯還是那個視窗
uj5u.com熱心網友回復：
來個10046吧，能看的清楚點。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/52483.html
標籤：基礎和管理