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
| drop database if exists mystix;
create database mystix;
\c mystix;
drop table if exists MTX_LIGNE_COMMANDE, MTX_SHOP_PHOTO, MTX_SHOP_ARTICLE, MTX_COMMANDE, MTX_MEMBER, MTX_SHOP_MARQUE, MTX_SHOP_CAT;
create table MTX_SHOP_CAT (shop_cat_num int primary key not null, shop_cat_libelle varchar(50) not null, shop_cat_image varchar(50) not null, shop_cat_order int not null);
create table MTX_SHOP_MARQUE (shop_marque_num int primary key not null, shop_marque_libelle varchar(50) not null, shop_marque_site varchar(50), shop_marque_logo varchar(50));
create table MTX_MEMBER (userid int primary key not null, username varchar(50) not null, password varchar(50) not null, email varchar(50) not null, nom varchar(50) not null, prenom varchar(50) not null, naissance varchar(10) not null, adresse varchar(50), ville varchar(50), CP varchar(5), pays varchar(50));
create table MTX_COMMANDE (commande_num int primary key not null, commande_fraisport float not null, commande_member int references MTX_MEMBER(userid) not null);
create table MTX_SHOP_ARTICLE (shop_article_num int primary key not null, shop_article_libelle varchar(50) not null, shop_article_description varchar(500), shop_article_prix float not null, shop_article_marque int references MTX_SHOP_MARQUE(shop_marque_num) not null, shop_article_cat int references MTX_SHOP_CAT(shop_cat_num) not null, shop_article_stock int not null, shop_article_miniature varchar(50) not null);
create table MTX_SHOP_PHOTO (shop_photo_num int primary key not null, shop_shop_photo varchar(50) not null, shop_photo_art int references MTX_SHOP_ARTICLE(shop_article_num) not null);
create table MTX_LIGNE_COMMANDE (ligne_num int not null, ligne_commande int references MTX_COMMANDE(commande_num) not null, ligne_article int references MTX_SHOP_ARTICLE(shop_article_num), ligne_prix float not null, primary key (ligne_num, ligne_commande));
insert into MTX_SHOP_CAT values (1,"Souris","souris.jpg",1);
insert into MTX_SHOP_CAT values (2,"Clavier","clavier.jpg",2);
insert into MTX_SHOP_CAT values (3,"Casque","casque.jpg",3);
insert into MTX_SHOP_MARQUE values (1,"Logitech","www.logitech.se","Logitech.jpg");
insert into MTX_SHOP_MARQUE values (2,"Razer","www.razer.com","Razer.jpg");
insert into MTX_SHOP_MARQUE values (3,"Roccat","www.roccat.org","roccat.jpg");
insert into MTX_SHOP_MARQUE values (4,"Icemat","www.icemat.fr","icemat.jpg");
insert into MTX_MEMBER values (1,"iBob","azerty","iBob@mysTix.fr","Lelong","Florian","15/06/1987","21 rue Tony Lainé","Poitiers","86000","France");
insert into MTX_MEMBER values (2,"Guigui","azerty","guigui@mystix.fr","Faucon","Guillaume","28/10/1987","11 rue de la Ganterie","Poitiers","86000","France");
insert into MTX_MEMBER values (3,"Pauleta","azerty","pauleta@psg.fr","Pauleta","Pedro","25/01/1974","3 rue des Bosquets","Poitiers","86000","France");
insert into MTX_COMMANDE values (1,18.50,1);
insert into MTX_COMMANDE values (2,8.50,1);
insert into MTX_SHOP_ARTICLE values (1,"RAZER Lycosa","Clavier de la marque Razer",79.90,2,2,9,"lycosa.jpg");
insert into MTX_SHOP_ARTICLE values (2,"Razer Copperhead Tempest","Souris pour joueurs",70,2,1,5,"copperhead.jpg");
insert into MTX_SHOP_ARTICLE values (3,"Icemat Siberia Blanc","Casque de haute qualité",90,4,3,0,"icematsiberia.jpg");
insert into MTX_LIGNE_COMMANDE values (1,1,1,79.90);
insert into MTX_LIGNE_COMMANDE values (2,1,2,80);
insert into MTX_LIGNE_COMMANDE values (3,1,3,65);
insert into MTX_LIGNE_COMMANDE values (1,2,2,65);
-- on créé la fonction supprime_commande
create or replace function supprime_commande()returns trigger as $$
-- on déclare les variables
declare
-- le corps du code
begin
delete from MTX_LIGNE_COMMANDE where ligne_commande = old.commande_num;
return old;
end;
$$ language 'plpgsql';
create trigger trigger1 before
delete on MTX_COMMANDE
for each row
execute procedure supprime_commande();
-- on créé la fonction calcul_ligne
create or replace function calcul_ligne()returns trigger as $$
-- on déclare les variables
declare
-- le corps du code
begin
update MTX_LIGNE_COMMANDE set ligne_num = ligne_num - 1 where ligne_num > old.ligne_num;
return old;
end;
$$ language 'plpgsql';
create trigger trigger2 before
delete on MTX_LIGNE_COMMANDE
for each row
execute procedure calcul_ligne(); |
