synchroniser des tables avec un trigger

[dev21]

Bonjour,
je débute dans l'utilisation des triggers Mysql.

Le contexte:
3 bases de données, dans chacune des bdd , une table 'product' avec une colonne 'quantity'.

Le but:
La valeur 'quantity' de chaque table 'product' doivent être synchronisée.
exemple: si update de la table db1.product, mises à jour des tables db2.product, db3.product. Si update de la table db2.product, mises à jour des tables db1.product, db3.product.


J'ai ajouter un trigger sur la table db1.product:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
delimiter $$
CREATE TRIGGER SYNCSTOCK
AFTER UPDATE ON db1.product
IF (NEW.reference != "") THEN
FOR EACH ROW
BEGIN
UPDATE db2.product SET quantity = NEW.quantity WHERE reference = NEW.reference;
UPDATE db3.product SET quantity = NEW.quantity WHERE reference = NEW.reference;
END IF;
END$$
delimiter ;

ma question, si j'adapte le même trigger sur les autres tables comme ceci:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
delimiter $$
CREATE TRIGGER SYNCSTOCK
AFTER UPDATE ON db2.product
IF (NEW.reference != "") THEN
FOR EACH ROW
BEGIN
UPDATE db1.product SET quantity = NEW.quantity WHERE reference = NEW.reference;
UPDATE db3.product SET quantity = NEW.quantity WHERE reference = NEW.reference;
END IF;
END$$
delimiter ;

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
delimiter $$
CREATE TRIGGER SYNCSTOCK
AFTER UPDATE ON db3.product
IF (NEW.reference != "") THEN
FOR EACH ROW
BEGIN
UPDATE db1.product SET quantity = NEW.quantity WHERE reference = NEW.reference;
UPDATE db2.product SET quantity = NEW.quantity WHERE reference = NEW.reference;
END IF;
END$$
delimiter ;

, cela va t-il engendrer une boucle infinie ? si oui comment puis-je contourner le problème ?

merci

[benangi]

Bonjour, un début de solution, tu testes si la quantité est différente avant de refaire un update.
Au passage j'ai ajouté la création du product s'il est absent.
Il faudrait peut un truc un peu similaire pour les insert...

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
117
118
119
 
delimiter $$
DROP TRIGGER IF EXISTS db1.SYNCSTOCK$$ 
CREATE TRIGGER db1.SYNCSTOCK
AFTER UPDATE ON db1.product
    FOR EACH ROW
    BEGIN
 
        DECLARE isRef2 INT DEFAULT 0;
        DECLARE isRef3 INT DEFAULT 0;
        DECLARE quantity2 INT DEFAULT NULL;
        DECLARE quantity3 INT DEFAULT NULL;
 
    IF (NEW.reference != "") THEN
 
        SELECT COUNT(reference) FROM db2.product WHERE reference =  NEW.reference INTO isRef2 ; 
        IF isRef2 !=0  THEN 
            SELECT quantity FROM db2.product WHERE reference =  NEW.reference INTO quantity2 ;
            IF (quantity2 != NEW.quantity ) THEN
                UPDATE db2.product SET quantity = NEW.quantity WHERE reference =  NEW.reference;
            END IF;
        ELSE   
            INSERT db2.product SET reference = NEW.reference, quantity = NEW.quantity; 
        END IF; 
 
 
 
        SELECT COUNT(reference) FROM db3.product WHERE reference =  NEW.reference INTO isRef3 ; 
        IF isRef3 !=0  THEN 
            SELECT quantity FROM db3.product WHERE reference =  NEW.reference INTO quantity3 ;
            IF (quantity3 != NEW.quantity ) THEN
                UPDATE db3.product SET quantity = NEW.quantity WHERE reference =  NEW.reference;
            END IF;
        ELSE   
            INSERT db3.product SET reference = NEW.reference, quantity = NEW.quantity; 
        END IF;
 
    END IF;
END$$
delimiter ;
 
delimiter $$
DROP TRIGGER IF EXISTS db2.SYNCSTOCK$$ 
CREATE TRIGGER db2.SYNCSTOCK
AFTER UPDATE ON db2.product
    FOR EACH ROW
    BEGIN
 
        DECLARE isRef1 INT DEFAULT 0;
        DECLARE isRef3 INT DEFAULT 0;
        DECLARE quantity1 INT DEFAULT NULL;
        DECLARE quantity3 INT DEFAULT NULL;
 
    IF (NEW.reference != "") THEN
 
        SELECT COUNT(reference) FROM db1.product WHERE reference =  NEW.reference INTO isRef1 ; 
        IF isRef1 !=0  THEN 
            SELECT quantity FROM db1.product WHERE reference =  NEW.reference INTO quantity1 ;
 
            IF (quantity1 != NEW.quantity ) THEN
                UPDATE db1.product SET quantity = NEW.quantity WHERE reference =  NEW.reference;
            END IF;
        ELSE   
            INSERT db1.product SET reference = NEW.reference, quantity = NEW.quantity; 
        END IF; 
 
        SELECT COUNT(reference) FROM db3.product WHERE reference =  NEW.reference INTO isRef3 ; 
        IF isRef3 !=0  THEN 
            SELECT quantity FROM db3.product WHERE reference =  NEW.reference INTO quantity3 ;
            IF (quantity3 != NEW.quantity ) THEN
                UPDATE db3.product SET quantity = NEW.quantity WHERE reference =  NEW.reference;
            END IF;
        ELSE   
            INSERT db3.product SET reference = NEW.reference, quantity = NEW.quantity; 
        END IF;
 
    END IF;
END$$
delimiter ;
 
 
delimiter $$
DROP TRIGGER IF EXISTS db3.SYNCSTOCK$$ 
CREATE TRIGGER db3.SYNCSTOCK
AFTER UPDATE ON db3.product
    FOR EACH ROW
    BEGIN
 
        DECLARE isRef1 INT DEFAULT 0;
        DECLARE isRef2 INT DEFAULT 0;
        DECLARE quantity1 INT DEFAULT NULL;
        DECLARE quantity2 INT DEFAULT NULL;
 
    IF (NEW.reference != "") THEN
 
        SELECT COUNT(reference) FROM db1.product WHERE reference =  NEW.reference INTO isRef1 ; 
        IF isRef1 !=0  THEN 
            SELECT quantity FROM db1.product WHERE reference =  NEW.reference INTO quantity1 ;
 
            IF (quantity1 != NEW.quantity ) THEN
                UPDATE db2.product SET quantity = NEW.quantity WHERE reference =  NEW.reference;
            END IF;
        ELSE   
            INSERT db1.product SET reference = NEW.reference, quantity = NEW.quantity; 
        END IF; 
 
        SELECT COUNT(reference) FROM db2.product WHERE reference =  NEW.reference INTO isRef2 ; 
        IF isRef2 !=0  THEN 
            SELECT quantity FROM db2.product WHERE reference =  NEW.reference INTO quantity2 ;
            IF (quantity2 != NEW.quantity ) THEN
                UPDATE db2.product SET quantity = NEW.quantity WHERE reference =  NEW.reference;
            END IF;
        ELSE   
            INSERT db2.product SET reference = NEW.reference, quantity = NEW.quantity; 
        END IF;
 
    END IF;
END$$
delimiter ;