Contrainte d'insertion sur une somme

Version imprimable

Bonjour,

Prenons un cas simple. Une table users

userid | bonus
1 | 1
1 | 1
1 | 0
1 | 0
2 | 1
2 | 1
2 | 1
3 | 1

Je voulais savoir si il existe un moyen de bloquer l'insertion ou l'update (retourner une erreur 'insertion impossible' par example) lorsque la somme des bonus est égale à 2.
Dans notre cas INSERT INTO users (id, bonus) VALUES(1, 1) serait rejetée car la somme des bonus pour ce userId est déjà égale à 2.

J'ai regardé autour des TRIGGER mais je patauge sérieusement (je suis un petit peu newbie)
Code:

1 2 3 4 5 6 7 8 CREATE TRIGGER check_bonus BEFORE INSERT ON users FOR EACH ROW BEGIN IF ((SELECT count(*) FROM users WHERE userid = NEW.userid AND bonus=1) >= 2) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'insertion impossible'; END IF; END;
Merci de votre aide précieuse.

Salut coincoin22.

Citation:

Envoyé par coincoin22

je suis un petit peu newbie

On n'est pas un petit peu newbie, on est débutant ou on ne l'est pas. Il n'y a pas de demi-mesure.

Citation:

Envoyé par coincoin22

Je voulais savoir si il existe un moyen de bloquer l'insertion ou l'update (retourner une erreur 'insertion impossible' par example) lorsque la somme des bonus est égale à 2.

Oui, c'est possible en faisant un déclencheur qui va recalculer le total de vos bonus et interdire l'insertion si la condition n'est pas correcte.
Je reprends votre exemple :

Code:

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
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`          integer unsigned  not null auto_increment primary key,
  `userid`      integer unsigned  not null,
  `bonus`       tinyint unsigned  not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`userid`,`bonus`) values
  (1,1),(1,1),(1,0),(1,0),
  (2,1),(2,1),(2,1),
  (3,1)
--------------
 
--------------
select * from `test`
--------------
 
+----+--------+-------+
| id | userid | bonus |
+----+--------+-------+
|  1 |      1 |     1 |
|  2 |      1 |     1 |
|  3 |      1 |     0 |
|  4 |      1 |     0 |
|  5 |      2 |     1 |
|  6 |      2 |     1 |
|  7 |      2 |     1 |
|  8 |      3 |     1 |
+----+--------+-------+
--------------
DROP TRIGGER IF EXISTS `verify`
--------------
 
--------------
CREATE TRIGGER `verify`
BEFORE INSERT ON `test`
FOR EACH ROW
BEGIN
  DECLARE _msg  varchar(255) default null;
  DECLARE _som  integer      default 0;
 
  set _som = (select sum(bonus) from test where userid = new.userid group by userid);
 
  IF (_som >=2 )then
     set _msg = concat('Bonus >=2 pour userid :', new.userid);
     SIGNAL SQLSTATE VALUE '07777' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 7777;
  END IF;
END
--------------
 
--------------
insert into `test` (`userid`,`bonus`) values  (1,0)
--------------
 
ERROR 7777 (07777) at line 72: Bonus >=2 pour userid :1
--------------
insert into `test` (`userid`,`bonus`) values  (2,0)
--------------
 
ERROR 7777 (07777) at line 73: Bonus >=2 pour userid :2
--------------
insert into `test` (`userid`,`bonus`) values  (3,1)
--------------
 
--------------
select * from `test`
--------------
 
+----+--------+-------+
| id | userid | bonus |
+----+--------+-------+
|  1 |      1 |     1 |
|  2 |      1 |     1 |
|  3 |      1 |     0 |
|  4 |      1 |     0 |
|  5 |      2 |     1 |
|  6 |      2 |     1 |
|  7 |      2 |     1 |
|  8 |      3 |     1 |
|  9 |      3 |     1 |
+----+--------+-------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Le userid 1 & 2 ont été rejeté, tandis que pour le userid 3, l'insertion a été faite.

Ne pas oublier que le rejet se fait sur la totalité de l'insert.
Avec votre déclencheur, vous ne devez pas faire des insertions multiples.

@+

28/04/2017, 14h29
skuatamad

Il faudrait ajouter un FOR UPDATE au SELECT afin de sérialiser le test en cas d'accès concurrent.
28/04/2017, 15h19
Artemus24

Salut skuatamad.

Oui vous avez raison, mais la demande de coincoin22 se porte sur le blocage d'une insertion si le bonus a atteint son maximum autorisé.
De ce fait, je n'ai donné que le déclencheur correspondant à sa demande.

@+
29/04/2017, 14h29
coincoin22

Merci pour vos réponses ! C'est parfait !

Seul problème... J'ai un hébergement mutualisé sur ovh et les triggers sont interdits ! Rrrrrrr !

A+
Coincoin22 (newbie totalement)

Salut coincoin22.

Citation:

Envoyé par coincoin22

J'ai un hébergement mutualisé sur ovh et les triggers sont interdits ! Rrrrrrr !

Vous en êtes sûr ? Cela me parait bizarre de ne pas accepter les déclencheurs.

Sinon, vous revenez à la bonne vieille méthode qui consiste à tester votre bonus avant de faire une insertion.

Code:

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
--------------
select * from `test`
--------------
 
+----+--------+-------+
| id | userid | bonus |
+----+--------+-------+
|  1 |      1 |     1 |
|  2 |      1 |     1 |
|  3 |      1 |     0 |
|  4 |      1 |     0 |
|  5 |      2 |     1 |
|  6 |      2 |     1 |
|  7 |      2 |     1 |
|  8 |      3 |     1 |
+----+--------+-------+
--------------
insert into `test` (`userid`,`bonus`)
  select          4 as userid,
                  1 as bonus
            from  dual
where not exists  ( select  sum(bonus) as total  from  `test`  where  userid = 4  group by  userid  having  total > 2)
--------------
 
--------------
select * from `test`
--------------
 
+----+--------+-------+
| id | userid | bonus |
+----+--------+-------+
|  1 |      1 |     1 |
|  2 |      1 |     1 |
|  3 |      1 |     0 |
|  4 |      1 |     0 |
|  5 |      2 |     1 |
|  6 |      2 |     1 |
|  7 |      2 |     1 |
|  8 |      3 |     1 |
|  9 |      4 |     1 |
+----+--------+-------+

01/05/2017, 20h31
coincoin22

Pour ovh, malheureusement oui... "TRIGGER command denied to user ..." et impossible de gérer les utilisateurs sql.

Alors... Merci pour cette bonne vieille méthode ;-)