Triggers

[30 min]

Un ensemble de règles de gestion est défini afin de gérer le stock des produits du projet MediaTek. La politique suivante a été admise :

  1. après chaque vente, la quantité vendue est soustraite de la quantité disponible indiquée dans une colonne de la table des produits,

  2. si cette quantité (disponible) est inférieure à un "seuil" alors insérer un avertissement dans une table de journalisation avec la date du jour, le numéro de produit, et stock restant après mise à jour.

Question

Créer un déclencheur permettant de mettre à jour la quantité disponible dans la table produit à chaque insertion dans la table ligne facture (traitement du point 1).

Indice

1
CREATE TRIGGER TR_STOCK_AUDIT
2
BEFORE INSERT ON ligne_fact

Indice

Il s'agit d'un trigger qui cherche à faire une modification pour chaque insertion : utilisez un trigger "for each row".

1
FOR EACH ROW

Indice

Utilisez une requête update pour effectuer la mise à jour avec une condition sur le numéro de produit.

1
UPDATE ...
2
SET ...
3
WHERE num=:new.produit;

Solution

1
CREATE TRIGGER TR_STOCK_AUDIT
2
BEFORE INSERT ON ligne_fact
3
FOR EACH ROW
4
BEGIN
5
  UPDATE PRODUIT p
6
  SET p.stock=p.stock - :new.qte
7
  WHERE p.num= :new.produit;
8
END;
9
/

Question

Testez votre trigger TR_STOCK_AUDIT en supprimant et en réinsérant les données des tables FACTURE et LIGNE_FACT.

Solution

1
delete from LIGNE_FACT;
2
delete from FACTURE;
1
-- INSERT INTO FACTURE, LIGNE_FACT
2
3
-- Facture 1
4
5
insert into FACTURE(num, client, date_etabli) values(
6
	1,
7
	2,
8
	to_date('20122000','DDMMYYYY')
9
);
10
11
insert into LIGNE_FACT(facture, produit, qte) values(
12
	1,
13
	1,
14
	1
15
);
16
17
insert into LIGNE_FACT(facture, produit, qte) values(
18
	1,
19
	2,
20
	2
21
);
22
23
-- Facture 2
24
25
insert into FACTURE(num, client, date_etabli) values(
26
	2,
27
	2,
28
	to_date('01012001','DDMMYYYY')
29
);
30
31
insert into LIGNE_FACT(facture, produit, qte) values(
32
	2,
33
	1,
34
	2
35
);
36
37
insert into LIGNE_FACT(facture, produit, qte) values(
38
	2,
39
	3,
40
	1
41
);
42
43
insert into LIGNE_FACT(facture, produit, qte) values(
44
	2,
45
	2,
46
	2
47
);
48
49
-- Facture 3
50
51
insert into FACTURE(num, client, date_etabli) values(
52
	3,
53
	2,
54
	to_date('17112004','DDMMYYYY')
55
);
56
57
insert into LIGNE_FACT(facture, produit, qte) values(
58
	3,
59
	1,
60
	2
61
);
62
63
insert into LIGNE_FACT(facture, produit, qte) values(
64
	3,
65
	2,
66
	4
67
);
68
69
insert into LIGNE_FACT(facture, produit, qte) values(
70
	3,
71
	3,
72
	1
73
);
74
75
-- Facture 4
76
77
insert into FACTURE(num, client, date_etabli) values(
78
	4,
79
	3,
80
	to_date('14012004','DDMMYYYY')
81
);
82
83
insert into LIGNE_FACT(facture, produit, qte) values(
84
	4,
85
	2,
86
	1
87
);
88
89
insert into LIGNE_FACT(facture, produit, qte) values(
90
	4,
91
	3,
92
	1
93
);
94
95
-- Facture 5
96
97
insert into FACTURE(num, client, date_etabli) values(
98
	5,
99
	4,
100
	to_date('19022004','DDMMYYYY')
101
);
102
103
insert into LIGNE_FACT(facture, produit, qte) values(
104
	5,
105
	1,
106
	2
107
);
108
109
insert into LIGNE_FACT(facture, produit, qte) values(
110
	5,
111
	3,
112
	3
113
);
114
115
insert into LIGNE_FACT(facture, produit, qte) values(
116
	5,
117
	2,
118
	4
119
);
120
121
-- Facture 6
122
123
insert into FACTURE(num, client, date_etabli) values(
124
	6,
125
	4,
126
	to_date('17032004','DDMMYYYY')
127
);
128
129
insert into LIGNE_FACT(facture, produit, qte) values(
130
	6,
131
	1,
132
	7
133
);
134
135
insert into LIGNE_FACT(facture, produit, qte) values(
136
	6,
137
	2,
138
	3
139
);
140
141
insert into LIGNE_FACT(facture, produit, qte) values(
142
	6,
143
	3,
144
	1
145
);
146
147
commit;
1
select * from PRODUIT;
Question
Créer la table de journalisation.
Solution
1
CREATE TABLE audit_stock (
2
  datealert DATE,
3
  produit NUMBER,
4
  stock NUMBER,
5
  CONSTRAINT FK_AUDIT_STOCK_PRODUIT FOREIGN KEY (produit) REFERENCES PRODUIT(num)
6
);
Question
Modifiez votre déclencheur de telle façon qu'après la mise à jour, il teste si le stock est inférieur à une valeur seuil de 5. Si le stock est inférieur à ce seuil, alors il devra insérer les informations adéquates dans la table de journalisation (traitement du point 2).
Solution
1
CREATE OR REPLACE TRIGGER TR_STOCK_AUDIT
2
BEFORE INSERT ON ligne_fact
3
FOR EACH ROW
4
DECLARE
5
  new_stock NUMBER;
6
  new_id NUMBER;
7
BEGIN
8
9
  UPDATE PRODUIT p
10
  SET p.stock = p.stock - :new.qte
11
  WHERE p.num = :new.produit;
12
									
13
  SELECT stock INTO new_stock
14
  FROM produit
15
  WHERE num = :new.produit;
16
17
  IF new_stock <= 5 THEN  
18
    INSERT INTO AUDIT_STOCK (datealert, produit, stock) 
19
    VALUES (
20
      sysdate,
21
      :new.produit,
22
      new_stock
23
    );
24
  END IF;
25
26
END;
27
/
28
SHOW ERRORS
Question
Testez à nouveau votre trigger jusqu'à obtenir au moins un message.
Solution
1
insert into FACTURE(num, client, date_etabli) values(
2
	7,
3
	2,
4
	to_date('20122000','DDMMYYYY')
5
);
6
7
insert into LIGNE_FACT(facture, produit, qte) values(
8
	7,
9
	1,
10
	90
11
);
12
13
select * from audit_stock;
14