synchroniser des tables avec un trigger

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

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 ;

---