Oracle insert the correct data is not successful-CodePudding

Do database maintenance in the company, need to be in a table into a batch of data, most are inserted into the success, there are a few can't insert and error, and a great god answer

The following is a part of the insert statement

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

First table key must not violate absolutely no duplicate data

Below is the error involved triggers

 CREATE OR REPLACE the TRIGGER "TIB_TD_TRUNK_IN 
"
BEFORE the 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. The START_DATE % type; 
V_END_DATE SETTLE. TD_TRUNK_IN. END_DATE % type; 
V_TRUNKGROUP_CONVERT SETTLE. TD_TRUNK_IN. TRUNKGROUP_CONVERT % type; 

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

IF (: NEW SWITCH_CODE='h01-2) THEN 
The UPDATE SETTLE. TD_TRUNK_IN 
The SET END_DATE=V_START_DATE 
WHERE SWITCH_CODE IN (' h01-2 ', 'H02) 
And TRUNKGROUP=V_TRUNKGROUP 
And TRUNK_TYPE=V_TRUNK_TYPE; 

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

The UPDATE SETTLE. TD_TRUNK_OUT 
The SET END_DATE=V_START_DATE 
WHERE SWITCH_CODE IN (' h01-2 ', 'H02) 
And TRUNKGROUP=V_TRUNKGROUP 
And TRUNK_TYPE=V_TRUNK_TYPE; 

INSERT INTO SETTLE. TD_TRUNK_OUT 
(SWITCH_CODE, 
TRUNKGROUP, 
TRUNK_TYPE, 
The START_DATE, 
END_DATE, 
TRUNKGROUP_CONVERT) 
VALUES 
(' h01-2, 
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, 
The 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 
The UPDATE SETTLE. TD_TRUNK_IN 
The 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, 
The START_DATE, 
END_DATE, 
TRUNKGROUP_CONVERT) 
VALUES 
(' H04, 
V_TRUNKGROUP, 
V_TRUNK_TYPE, 
V_START_DATE, 
V_END_DATE, 
V_TRUNKGROUP_CONVERT); 

The UPDATE SETTLE. TD_TRUNK_OUT 
The 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, 
The 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, 
The 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;

CodePudding user response:

first table key must not violate absolutely no duplicate data

If the system violated the only constraint, it must be violated,

But you just put the PK is disabled, then execute the insert statement, you after completion of execution, you inquire again, whether have duplicate values,

CodePudding user response:

reference 1st floor selling fruit net reply:

first table key must not violate absolutely no duplicate data

If the system violated the only constraint, it must be violated,

nullnullnullnullnullnullnullnull