1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
|
CREATE SEQUENCE DOCTYPE_ID_SEQ
START WITH 1
NOMAXVALUE
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;
CREATE SEQUENCE DOCFIELDS_ID_SEQ
START WITH 1
NOMAXVALUE
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;
CREATE SEQUENCE DOCFIELDVALUES_ID_SEQ
START WITH 1
NOMAXVALUE
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;
CREATE TABLE docfieldslabel (
doc_field_id number(4) NOT NULL,
field_label char(80) NOT NULL,
locale char(80) NOT NULL );
aLTER TABLE DOCFIELDSLABEL ADD (
PRIMARY KEY (doc_field_id)
USING INDEX);
CREATE TABLE doctype (
doc_type_id number(4) not null,
doc_type_name char(255) not null,
primary key (doc_type_id));
CREATE TABLE docfields (
id number(4) not null,
doc_type_id number(4) not null ,
field_name char(80) not null,
field_position number(4) not null,
field_type char(80) not null,
field_values char(80) not null,
field_size number(38) not null,
searchable number(4) not null,
required number(4) not null,
primary key (id)
);
CREATE TABLE docfieldvalues (
id number(4) not null,
file_id number(4) not null ,
field_name char(80) not null,
field_value char(80) not null,
primary key (id)
);
alter table files add (doctype number(4));
CREATE OR REPLACE TRIGGER DOCTYPE_ID_TRIGGER
BEFORE INSERT ON DOCTYPE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
SELECT doctype_ID_SEQ.NEXTVAL INTO :NEW.doc_type_id FROM DUAL;
END;
/
show errors;
CREATE OR REPLACE TRIGGER DOCFIELDS_ID_TRIGGER
BEFORE INSERT ON DOCFIELDS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
SELECT docfields_ID_SEQ.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/
show errors;
CREATE OR REPLACE TRIGGER DOCFIELDVALUES_ID_TRIGGER
BEFORE INSERT ON DOCFIELDVALUES
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
SELECT docfieldvalues_ID_SEQ.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/
show errors;
INSERT INTO doctype (doc_type_name) values ('Default');
|
