小弟遇到一个奇怪的问题，oracle数据库触发器用于监视数据库的插入更新和删除，并声称日志。出现了一个奇怪的现象，如下例如A表设置了itemcode和orgcode唯一索引。但是监视该表的触发器，日志发现了连续的插入两条相同itemcode和orgcode的记录。附上图。以上是日志表记录，updateno是增长主键，opertime是操作时间。
请问出现这种情况是什么导致的呢？
请问出现这种情况是什么导致的呢？
(
itemid VARCHAR2(36) not null,
orgcode VARCHAR2(20) not null,
itemcode VARCHAR2(15) not null,
itemname VARCHAR2(200) not null,
specs VARCHAR2(50),
spellcode VARCHAR2(100),
wbcode VARCHAR2(100),
unitprice NUMBER(10,2),
stockunit VARCHAR2(20),
feecode VARCHAR2(4) not null,
outinvocode VARCHAR2(3),
ininvocode VARCHAR2(3),
nhcentercode VARCHAR2(20),
itemstatus VARCHAR2(1) not null,
exedeptcode VARCHAR2(20),
exedeptname VARCHAR2(30),
opercode VARCHAR2(6),
opername VARCHAR2(30),
opertime DATE
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64M
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table FIN_UNDRUGINFO
is 'Fin_收费项目表';
-- Add comments to the columns
comment on column FIN_UNDRUGINFO.itemid
is '诊疗项目ID';
comment on column FIN_UNDRUGINFO.orgcode
is '医疗机构编码';
comment on column FIN_UNDRUGINFO.itemcode
is '项目编码';
comment on column FIN_UNDRUGINFO.itemname
is '项目名称';
comment on column FIN_UNDRUGINFO.specs
is '规格';
comment on column FIN_UNDRUGINFO.spellcode
is '拼音简码';
comment on column FIN_UNDRUGINFO.wbcode
is '五笔简码';
comment on column FIN_UNDRUGINFO.unitprice
is '项目单价';
comment on column FIN_UNDRUGINFO.stockunit
is '单位';
comment on column FIN_UNDRUGINFO.feecode
is '最小核算科目编码,编码不能修改';
comment on column FIN_UNDRUGINFO.outinvocode
is '门诊发票科目';
comment on column FIN_UNDRUGINFO.ininvocode
is '住院发票科目';
comment on column FIN_UNDRUGINFO.nhcentercode
is '农合中心项目编码';
comment on column FIN_UNDRUGINFO.itemstatus
is '项目状态： １在用 ０停用';
comment on column FIN_UNDRUGINFO.exedeptcode
is '执行科室-主要指医技科室';
comment on column FIN_UNDRUGINFO.exedeptname
is '执行科室名称';
comment on column FIN_UNDRUGINFO.opercode
is '操作员工号';
comment on column FIN_UNDRUGINFO.opername
is '操作员姓名';
comment on column FIN_UNDRUGINFO.opertime
is '操作时间';
-- Create/Recreate indexes
create unique index IDX_FIN_UNDRUGINFO_1 on FIN_UNDRUGINFO (ITEMCODE, ORGCODE)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 34M
next 1M
minextents 1
maxextents unlimited
);
create index IDX_FIN_UNDRUGINFO_2 on FIN_UNDRUGINFO (ORGCODE, FEECODE)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 29M
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table FIN_UNDRUGINFO
add constraint PK_FIN_UNDRUGINFO primary key (ITEMID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 21M
next 1M
minextents 1
maxextents unlimited
);
alter table FIN_UNDRUGINFO
add constraint PK_ORGCODE_ITEM_CODE unique (ORGCODE, ITEMCODE);
触发器：
CREATE OR REPLACE TRIGGER trg_undruginfo
AFTER DELETE OR INSERT OR UPDATE ON fin_undruginfo
FOR EACH ROW
DECLARE
v_lognum VARCHAR2(3);
var_orgcoce VARCHAR2(20);
BEGIN IF deleting THEN
--获取当前定点对应的LOG表编号
SELECT license
INTO v_lognum
FROM up_org_unit_ext
WHERE orgcode = :old.orgcode;
--删除操作增加删除SQL
INSERT INTO localdbupdatelog
(updateno,
orgcode,
tablename,
updatesql,
sqlparameter,
opertime,
opertype,
entityid)
VALUES
(seq_localdb_update_no.nextval,
'' || :old.orgcode || '',
'FIN_UNDRUGINFO',
'DELETE FROM FIN_UNDRUGINFO WHERE ITEMID=?',
'[''' || :old.itemid || ''']',
SYSDATE,
'DELETE',
'' || :old.itemid || '');
ELSIF inserting THEN
var_orgcoce := :new.Orgcode;
--获取当前定点对应的LOG表编号 SELECT license
INTO v_lognum
FROM up_org_unit_ext
WHERE orgcode = var_orgcoce;
INSERT INTO localdbupdatelog
(updateno,
orgcode,
tablename,
updatesql,
sqlparameter,
opertime,
opertype,
entityid)
VALUES
(seq_localdb_update_no.nextval,
'' || :new.orgcode || '',
'FIN_UNDRUGINFO',
'INSERT INTO FIN_UNDRUGINFO(ITEMID,ORGCODE,ITEMCODE,ITEMNAME,SPECS,SPELLCODE,WBCODE,UNITPRICE,STOCKUNIT,FEECODE,OUTINVOCODE,' ||
'ININVOCODE,NHCENTERCODE,ITEMSTATUS,EXEDEPTCODE,EXEDEPTNAME,OPERCODE,OPERNAME,OPERTIME)' ||
' VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
'[''' || :new.itemid || ''',''' || :new.orgcode || ''',''' ||
:new.itemcode || ''',''' || REPLACE(:new.itemname, '''', '') ||
''',''' || REPLACE(:new.specs, '''', '') || ''',''' ||
REPLACE(:new.spellcode, '''', '') || ''',''' ||
REPLACE(:new.wbcode, '''', '') || ''',''' || :new.unitprice ||
''',''' || :new.stockunit || ''',''' || :new.feecode ||
''',''' || :new.outinvocode || ''',''' || :new.ininvocode ||
''',''' || :new.nhcentercode || ''',''' || :new.itemstatus ||
''',''' || :new.exedeptcode || ''',''' ||
REPLACE(:new.exedeptname, '''', '') || ''',''' ||
:new.opercode || ''',''' || :new.opername || ''',''' ||
:new.opertime || ''']',
SYSDATE,
'INSERT',
'' || :new.itemid || '');
ELSIF updating THEN
--当前值
var_orgcoce := :old.orgcode;
--获取当前定点对应的LOG表编号
SELECT license
INTO v_lognum
FROM up_org_unit_ext
WHERE orgcode = :old.orgcode;
--对于UPDATE先删除该变动对象所有的UPDATE记录，然后重新生成最新UPDATE记录
DELETE FROM localdbupdatelog
WHERE entityid = :old.itemid
AND opertype = 'UPDATE'
AND tablename = 'FIN_UNDRUGINFO';
INSERT INTO localdbupdatelog
(updateno,
orgcode,
tablename,
updatesql,
sqlparameter,
opertime,
opertype,
entityid)
VALUES
(seq_localdb_update_no.nextval,
'' || :new.orgcode || '',
'FIN_UNDRUGINFO',
'UPDATE FIN_UNDRUGINFO ' ||
'SET ORGCODE=?,ITEMCODE=?,ITEMNAME=?,SPECS=?,SPELLCODE=?,WBCODE=?,' ||
'UNITPRICE=?,STOCKUNIT=?,FEECODE=?,OUTINVOCODE=?,ININVOCODE=?,NHCENTERCODE=?,' ||
'ITEMSTATUS=?,EXEDEPTCODE=?,EXEDEPTNAME=?,OPERCODE=?,OPERNAME=?,OPERTIME=? WHERE ITEMID=?',
'[''' || :new.orgcode || ''',''' || :new.itemcode || ''',''' ||
REPLACE(:new.itemname, '''', '') || ''',''' ||
REPLACE(:new.specs, '''', '') || ''',''' || :new.spellcode ||
''',''' || :new.wbcode || ''',''' || :new.unitprice || ''',''' ||
:new.stockunit || ''',''' || :new.feecode || ''',''' ||
:new.outinvocode || ''',''' || :new.ininvocode || ''',''' ||
:new.nhcentercode || ''',''' || :new.itemstatus || ''',''' ||
:new.exedeptcode || ''',''' ||
REPLACE(:new.exedeptname, '''', '') || ''',''' ||
:new.opercode || ''',''' || :new.opername || ''',''' ||
:new.opertime || ''',''' || :new.itemid || ''']',
SYSDATE,
'UPDATE',
'' || :old.itemid || '');
END IF;END trg_undruginfo;