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
Best Answer
Given your error (
"OFUSER_PK"
), this is an indication that the primary key on theOFUSER
table is the problem. Check your insert statements forOFUSER
- something seems wonky there.