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
| create table YD_TYPE_DOC
( YD_ident integer auto_increment primary key
, YD_code char(4) not null unique
, YD_libelle varchar(50) not null
)
;
insert into YD_TYPE_DOC (YD_code, YD_libelle)
values ('NOTC', 'Notice')
, ('PLAN', 'Plan')
, ('SCHM', 'Schéma')
;
create table TC_TABL_CHRONO
( TC_table char(20) not null
, TC_colonne char(20) not null
, TC_chrono integer not null
, primary key (TC_table, TC_colonne)
)
;
insert into TC_TABL_CHRONO(TC_table, TC_colonne, TC_chrono)
values ('DO_DOCUMENT', 'DO_numero', 0)
;
create table DO_DOCUMENT
( DO_ident integer auto_increment primary key
, DO_numero integer not null unique
, DO_titre varchar(50) not null
, DO_date date not null
, YD_ident integer not null
, constraint DOFK01
foreign key(YD_ident)
references YD_TYPE_DOC(YD_ident)
on delete restrict on update cascade
)
;
update TC_TABL_CHRONO
set TC_chrono = TC_chrono + 1
where TC_table = 'DO_DOCUMENT'
and TC_colonne = 'DO_numero'
;
-- insertion d'un document de type "notice" dans la table des documents
insert into DO_DOCUMENT (DO_numero, DO_titre, DO_date, YD_ident)
values ( (select max(TC_chrono)
from TC_TABL_CHRONO
where TC_table = 'DO_DOCUMENT'
and TC_colonne = 'DO_numero')
, 'mon document'
, current_date()
, (select YD_ident
from YD_TYPE_DOC
where YD_code = 'NOTC')
)
;
commit
;
update TC_TABL_CHRONO
set TC_chrono = TC_chrono + 1
where TC_table = 'DO_DOCUMENT'
and TC_colonne = 'DO_numero'
;
-- insertion d'un document de type "plan" dans la table des documents
insert into DO_DOCUMENT (DO_numero, DO_titre, DO_date, YD_ident)
values ( (select max(TC_chrono)
from TC_TABL_CHRONO
where TC_table = 'DO_DOCUMENT'
and TC_colonne = 'DO_numero')
, 'mon plan'
, current_date()
, (select YD_ident
from YD_TYPE_DOC
where YD_code = 'PLAN')
)
;
commit
;
select * from DO_DOCUMENT
; |
