, the table above is log, updateno is growing primary keys, opertime is operating time,
Could you tell me what happen to it?
CodePudding user response:
The build table statements, trigger statement, insert statement, the three text,CodePudding user response:
The main table:The create table FIN_UNDRUGINFO
(
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 (1, 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
)
In tablespace USERS
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
The initial 64 m
Next 1 m
Minextents 1
The maxextents unlimited
);
- Add comments to the table
Comment on the table FIN_UNDRUGINFO
Is' Fin_ charging projects table;
- Add comments to the columns
Comment on the column FIN_UNDRUGINFO. Itemid
ID 'is' diagnosis and treatment projects;
Comment on the column FIN_UNDRUGINFO.org code
Is' coding of medical institutions;
Comment on the column FIN_UNDRUGINFO. Itemcode
Is' program code ';
Comment on the column FIN_UNDRUGINFO. Itemname
Is the 'project name';
Comment on the column FIN_UNDRUGINFO. Specs
Is the 'specification';
Comment on the column FIN_UNDRUGINFO. Spellcode
Is' pinyin in code;
Comment on the column FIN_UNDRUGINFO. Wbcode
Is' wubi in code;
Comment on the column FIN_UNDRUGINFO. Unitprice
Is' project unit price;
Comment on the column FIN_UNDRUGINFO. Stockunit
Is' units';
Comment on the column FIN_UNDRUGINFO. Feecode
Is' minimum business accounting code, code cannot be modified.
Comment on the column FIN_UNDRUGINFO. Outinvocode
Is' outpatient invoice subjects';
Comment on the column FIN_UNDRUGINFO. Ininvocode
Is' hospitalization invoice subjects';
Comment on the column FIN_UNDRUGINFO. Nhcentercode
Is' farming combined center project coding;
Comment on the column FIN_UNDRUGINFO. Itemstatus
Is' project status: 1 with zero downtime.
Comment on the column FIN_UNDRUGINFO. Exedeptcode
Is' executive departments - mainly refers to the medical department.
Comment on the column FIN_UNDRUGINFO. Exedeptname
Is' executive department name;
Comment on the column FIN_UNDRUGINFO. Opercode
Is' operator working;
Comment on the column FIN_UNDRUGINFO. Opername
Is' operator's name;
Comment on the column FIN_UNDRUGINFO. Opertime
Is' operation time;
- the Create/Recreate indexes
Create unique index IDX_FIN_UNDRUGINFO_1 on FIN_UNDRUGINFO (ITEMCODE, ORGCODE)
In tablespace USERS
Pctfree 10
Initrans 2
Maxtrans 255
Storage
(
Initial 34 m
Next 1 m
Minextents 1
The maxextents unlimited
);
The create index IDX_FIN_UNDRUGINFO_2 on FIN_UNDRUGINFO (ORGCODE FEECODE)
In tablespace USERS
Pctfree 10
Initrans 2
Maxtrans 255
Storage
(
Initial 29 m
Next 1 m
Minextents 1
The maxextents unlimited
);
- the Create/Recreate primary, unique and foreign key constraints
The alter table FIN_UNDRUGINFO
Add the constraint PK_FIN_UNDRUGINFO primary key (ITEMID)
Using the index
In tablespace USERS
Pctfree 10
Initrans 2
Maxtrans 255
Storage
(
Initial 21 m
Next 1 m
Minextents 1
The maxextents unlimited
);
The alter table FIN_UNDRUGINFO
Add the constraint PK_ORGCODE_ITEM_CODE unique (ORGCODE, ITEMCODE);
Trigger:
The CREATE OR REPLACE the TRIGGER trg_undruginfo
AFTER the DELETE OR INSERT OR UPDATE ON fin_undruginfo
FOR EACH ROW
DECLARE
V_lognum VARCHAR2 (3);
Var_orgcoce VARCHAR2 (20);
The BEGIN
IF deleting THEN
- access to current fixed point corresponding LOG table number
The SELECT license
INTO v_lognum
The FROM up_org_unit_ext
WHERE orgcode=: old.org code;
Delete, add delete SQL
INSERT INTO localdbupdatelog
(updateno,
Orgcode,
Tablename,
Updatesql,
Sqlparameter,
Opertime,
Opertype,
Entityid)
VALUES
(seq_localdb_update_no. Nextval,
"' | | : old.org code | | ',
'FIN_UNDRUGINFO'
'DELETE FROM FIN_UNDRUGINFO WHERE ITEMID=? '
'['' '| | : old. Itemid | |' '] ',
SYSDATE,
'DELETE'
"' | | : old. Itemid | |");
ELSIF inserting THEN
Var_orgcoce:=: new.Org code;
- access to current fixed point corresponding LOG table number
The SELECT license
INTO v_lognum
The 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.org code | | ',
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull