Discussion :

[zebiloute]

Bonjour.

Je viens de découvrir les triggers et désirant apprendre, j'essai dans créer un afin de prévenir l'insertion de valeurs négatives ou 0 dans une table en utilisant un trigger.

table1 :
col1 : Integer
col2 : Varchar(32)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO table1 (col1, col2) VALUES (1, 'azerty');

Dans ce cas c'est bon : On insert.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO table1 (col1, col2) VALUES (0, 'azerty');

Ce n'est pas bon : On insert pas parceque col1 <=0.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UPDATE table1 SET col1 = -1, col2 = 'azerty' WHERE col1 = '1' AND col2 = 'azerty'

Dans ce cas : On supprime l'enregistrement parceque col1 <= 0


Dans le cas de l'update je pense qu'il y aura un soucis de vérouillage de la table parceque j'ai ce genre de problème avec un trigger after update sur la table1.

Merci.

[berceker united]

Tu n'as pas besoin de faire un trigger pour cela. Il suffit de rendre ton champ UNSIGNED. Ainsi, seul les valeurs >=0 peuventre être inséré.
Mais admettons :
Il y a un autre problème. Tu modifies l'information après l'insertion.
Il suffit d'executer le trigger en BEFOR UPDATE ou BEFOR INSERT. Tu contrôles avant qu'elle soit enregistré. Ainsi tu n'as pas à retourner dans la table pour modifier la données. Je rajouterais en plus que c'est pas trop possible.

[zebiloute]

Merci.

Je fais ça pour apprendre à utiliser les triggers (c'est un exercice) parceque là je peine un peu. Je n'arrive pas à lui dire de rien faire, ou de détruire l'enregistrement dans le cas ou les conditions ne sont pas remplies.

[berceker united]

Tu veux refuser l'enregistrement ou reformater la données mal formé ?

[zebiloute]

Je dirais les deux.

1 - Ne pas enregistrer si col1<=0
exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO table1 (col1, col2) VALUES (0, 'azerty');

Dans un second temps :

2 - Supprimer automatiquement un enregistrement quand la valeur de col1<=0
exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UPDATE table1 SET col1 = -1, col2 = 'azerty' WHERE col1 = '1' AND col2 = 'azerty'

[berceker united]

Tu ne peux pas empecher une opération d'écriture si elle a été lancé. Le trigger n'est pas tellement fait pour cela. Il faut dans ce cas là, il est préférable d'utiliser des procedures stockés.

[Antoun]

Cas 1/ le trigger ne peut EXPLICITEMENT refuser l'action. Par contre il peut la faire échouer, par ex. :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
CREATE TRIGGER table1_BI BEFORE INSERT ON table1
FOR EACH ROW
IF new.col1 <= 0 THEN SET new.col1 = 'alpha' END IF ;

Cas 2/ on ne peut pas utiliser la même technique parce que si le trigger échoue, l'UPDATE échouera aussi, et la ligne subsistera avec son ancienne valeur. Si tu as une ID sur la ligne, tu peux faire un AFTER UPDATE :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
CREATE TRIGGER table1_AU AFTER UPDATE ON table1
FOR EACH ROW
IF new.col1 <= 0 THEN DELETE FROM table1 WHERE table1.ID = new.ID ;

[zebiloute]

Merci pour l'information.

Là ça marche au niveau syntaxe, mais pas en fonctionnement.

MySQL : 5.0.41

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
`col1` INT( 4 ) NOT NULL ,
`col2` VARCHAR( 32 ) NOT NULL
) ENGINE = innodb;
 
 
delimiter //
DROP TRIGGER IF EXISTS trig1//
CREATE TRIGGER trig1
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN  
    IF new.col1 <= 0 THEN
        SET new.col1 = 'alpha';
    END IF;
END;//
delimiter ;
 
 
INSERT INTO `table1` (`col1`,`col2`)
VALUES	('1','a'),
				('2','b'),
				('0','*'),
				('-1','*');
 
SELECT * FROM `table1`;

Résultat :

+------+------+
| col1 | col2 |
+------+------+
| 1 | a |
| 2 | b |
| 0 | * |
| 0 | * |
+------+------+

[berceker united]

Normal car tu lui donne une chaine de caractère. Donc il te place un zero. Par contre, heureusement que Mysql est intelligent car en placant un zero il aurait pu tourner en rond.
Donc ce test montre bien que le trigger s'execute qu'une seul fois.

[zebiloute]

Ok.

Le Trigger dans ce cas remplace la valeur négative par une chaine de caractère, et il me semblait que le transtypage n'était pas automatique.

Pour palier au problème je génère une erreur en appelant une procedure qui n'existe pas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
delimiter //
DROP TRIGGER IF EXISTS trig1//
CREATE TRIGGER trig1
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN  
    IF new.col1 <= 0 THEN
	CALL NOM_ERREUR();
    END IF;
END;//
delimiter ;

INSERT INTO `table1` (`col1` ,`col2`)
VALUES ('-1', '-1')

#1305 - PROCEDURE jawxshop.NOM_ERREUR does not exist

Pour ce qui est de l'UPDATE :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
delimiter //
DROP TRIGGER IF EXISTS trig2//
CREATE TRIGGER trig2
AFTER UPDATE ON table1
FOR EACH ROW
BEGIN  
    IF new.col1 <= 0 THEN
	DELETE FROM table1 WHERE table1.col1 = new.col1 ;
    END IF;
END;//
delimiter ;

#1442 - Can't update table 'table1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Il doit y avoir un soucis de concurrence d'accès à la table. étant donné qu'elle est vérouillée par la procédure d'update, il n'est pas possible d'effectuer d'autres opérations.

[berceker united]

Il y a un cas ou j'ai réussi à écrire dans une table concurrent. Je me rappelle pas trop comment j'ai réussi à m'en sortir. Bref, L'idée de provoquer une erreur n'est pas trop mal mais c'est une technique de cabot

J'ai rencontré le même problème que toi. J'ai résolu le problème en gérant toute les entrées de la base de données en passante par les procédures stocké. En gros, il n'est pas possible de faire des enregistrements directement sur une table. Il faut obligatoirement passer par une procédure stocké. Ainsi, tu peux contrôler les entrés.

[zebiloute]

Pas facile. au moins j'aurais appris des trucs sur les triggers. Maintenant je vais donc m'interresser aux procédures. En tout cas merci pour l'aide .

[berceker united]

Il est prévus que je fasse un article sur les Procédures stocké, Fonctions et Triggers.
Il y a des petites choses à savoir qui n'est pas forcément dit dans le documentation officiel.