0

I have code create table

CREATE TABLE OFPHONE
(
  PHONEID     INTEGER                           NOT NULL,
  HOTLINE     VARCHAR2(20 CHAR),
  EXT         VARCHAR2(20 CHAR),
  PHONE       VARCHAR2(30 CHAR),
  PASSWORD    VARCHAR2(30 BYTE),
  NAME        VARCHAR2(30 BYTE),
  CREATEDATE  TIMESTAMP(6)                      DEFAULT CURRENT_TIMESTAMP
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;

COMMENT ON COLUMN OFPHONE.PASSWORD IS 'Password';



CREATE UNIQUE INDEX OFPHONE_PK ON OFPHONE
(PHONEID)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
CREATE OR REPLACE TRIGGER TRIGGER_OFPHONE_AFTER_INSERT2
AFTER INSERT
ON OFPHONE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
    tmp varchar2(20);


begin


    insert into ofphone_tmp (PHONEID,HOTLINE,EXT,PHONE,PASSWORD,NAME,CREATEDATE)
    values (:new.PHONEID,:new.HOTLINE,:new.EXT,:new.PHONE,:new.PASSWORD,:new.NAME, sysdate); 

    insert into OFUSER(USERNAME,PLAINPASSWORD,NAME,EMAIL,CREATIONDATE,MODIFICATIONDATE)
    values(:NEW.PHONE,:NEW.PASSWORD,:NEW.NAME,trim(' ' from :NEW.NAME)||'@localhost',sysdate,sysdate);

    --commit;

   for x in 
   (select  phone,name,hotline from ofphone_tmp where hotline = :NEW.hotline and phone <> :new.phone) loop

        insert into ofRoster values (SEQ_OFROSTER.nextval,:new.PHONE,x.phone||'@localhost',3,-1,-1,X.name);

        insert into ofRoster values (SEQ_OFROSTER.nextval,x.phone,:new.PHONE||'@localhost',3,-1,-1,:new.name);


   end loop;

end;
/

I tried debug code snippet

But I checked the details of the value of the block for cur in (has data) and select SEQ_OFPHONE (has data) are valid.

Please tell me this error case.

begin

    for cur in(select
                c.COMPANY_MOBILE,
                e.EMPLOYEE_NAME,
                E.MOBILE_PHONE,
                E.EMPLOYEE_NO,
                E.EMPLOYEE_PWD,
                E.Email
             from TAB_XN_EMPLOYEE e
            inner join TAB_XN_COMPANY c
            on e.COMPANY_ID = c.COMPANY_ID
            and c.COMPANY_MOBILE  = '0912400604'
            ) loop
            insert into ofphone( PHONEID,HOTLINE,EXT,PHONE,PASSWORD,NAME,CREATEDATE)
            values(SEQ_OFPHONE.nextval,cur.COMPANY_MOBILE,cur.EMPLOYEE_NO,
            cur.MOBILE_PHONE,cur.EMPLOYEE_PWD,cur.EMPLOYEE_NAME,sysdate);

    end loop;

end;

Error

ORA-00001: unique constraint (MEG.OFUSER_PK) violated ORA-06512: at "MEG.TRIGGER_OFPHONE_AFTER_INSERT2", line 20 ORA-04088: error during execution of trigger 'MEG.TRIGGER_OFPHONE_AFTER_INSERT2' ORA-06512: at line 15

1

Given your error ("OFUSER_PK"), this is an indication that the primary key on the OFUSER table is the problem. Check your insert statements for OFUSER - something seems wonky there.

0

Its look like that in target table already have the same data that you are inserting so you no longer need to insert same again as primary key going violate. But if you want to go through the precess and want

"if found duplicate row then skip and not found then insert" method

then you can use
"IGNORE_ROW_ON_DUPKEY_INDEX" to perform same. follow below format/example to achieve that.

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX (TARGET_TABLE_NAME (Primery_key_column1,Primery_key_column2,..)) */ into TARGET_TABLE_NAME
SELECT column1,column2,column3.......
from source_table_NAME.
where conditions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.