Bonjour,
j'ai créé un événement sur une table mysql. Le but est de créer des compteurs en fonction d'un état. Là l'état est stocké dans une table cem_states.
J'ai un premier problème sur la première ligne, j'aimerais savoir si marche_id existe dans la table cem_counts, si c'est le cas, je mets à jour cette ligne sinon, je la créé. Seulement là je créé la ligne même si elle existe. Je mets la requête SQL :
/*SI marche_id existe dans la table cem_count, je mets àjour la table sinon je créé la ligne*/
IF (SELECT marche_id FROM cem_counts WHERE marche_id = New.marche_id) THEN
BEGIN
UPDATE cem_counts SET
imb_count_bpt_a_faire_valider = (SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '1')),
imb_el_bpt_a_faire_valider = (SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '1')),
imb_count_bon_pour_etudes = (SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '2')),
imb_el_bon_pour_etudes = (SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '2')),
imb_count_bon_pour_travaux_valides = (SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '3')),
imb_el_bon_pour_travaux_valides = (SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '3')),
imb_count_commande_acces_a_lancer = (SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '4')),
imb_el_commande_acces_a_lancer = (SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '4')),
imb_count_commande_acces_a_valider = (SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '5')),
imb_el_commande_acces_a_valider = (SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '5')),
imb_count_retour_fiche_immeuble = (SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '6')),
imb_el_retour_fiche_immeuble = (SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '6')),
imb_count_travaux_a_assigner = (SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '7')),
imb_el_travaux_a_assigner = (SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '7')),
imb_count_travaux_en_retard = (SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '8')),
imb_el_travaux_en_retard = (SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '8')),
imb_count_recollements_a_effectuer = (SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '9')),
imb_el_recollements_a_effectuer = (SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '9')),
imb_count_travaux_terminés = (SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '10')),
imb_el_travaux_terminés = (SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '10')),
imb_count_sites_raccordes_optimum = (SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '11')),
imb_el_sites_raccordes_optimum = (SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '11'))
WHERE cem_counts.marche_id = NEW.marche_id;
END;
ELSE
BEGIN
INSERT INTO cem_counts (
marche_id,
imb_count_bpt_a_faire_valider,
imb_el_bpt_a_faire_valider,
imb_count_bon_pour_etudes,
imb_el_bon_pour_etudes,
imb_count_bon_pour_travaux_valides,
imb_el_bon_pour_travaux_valides,
imb_count_commande_acces_a_lancer,
imb_el_commande_acces_a_lancer,
imb_count_commande_acces_a_valider,
imb_el_commande_acces_a_valider,
imb_count_retour_fiche_immeuble,
imb_el_retour_fiche_immeuble,
imb_count_travaux_a_assigner,
imb_el_travaux_a_assigner,
imb_count_travaux_en_retard,
imb_el_travaux_en_retard,
imb_count_recollements_a_effectuer,
imb_el_recollements_a_effectuer,
imb_count_travaux_terminés,
imb_el_travaux_terminés,
imb_count_sites_raccordes_optimum,
imb_el_sites_raccordes_optimum
)
SELECT
/*marche_id*/
imb.marche_id,
/*imb_count_bpt_a_faire_valider*/
(SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '1')),
/*imb_el_bpt_a_faire_valider*/
(SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '1')),
/*imb_count_bon_pour_etudes*/
(SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '2')),
/*imb_el_bon_pour_etudes*/
(SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '2')),
/*imb_count_bon_pour_travaux_valides*/
(SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '3')),
/*imb_el_bon_pour_travaux_valides*/
(SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '3')),
/*imb_count_commande_acces_a_lancer*/
(SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '4')),
/*imb_el_commande_acces_a_lancer*/
(SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '4')),
/*imb_count_commande_acces_a_valider*/
(SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '5')),
/*imb_el_commande_acces_a_valider*/
(SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '5')),
/*imb_count_retour_fiche_immeuble*/
(SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '6')),
/*imb_el_retour_fiche_immeuble*/
(SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '6')),
/*imb_count_travaux_a_assigner*/
(SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '7')),
/*imb_el_travaux_a_assigner*/
(SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '7')),
/*imb_count_travaux_en_retard*/
(SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '8')),
/*imb_el_travaux_en_retard*/
(SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '8')),
/*imb_count_recollements_a_effectuer*/
(SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '9')),
/*imb_el_recollements_a_effectuer*/
(SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '9')),
/*imb_count_travaux_terminés*/
(SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '10')),
/*imb_el_travaux_terminés*/
(SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '10')),
/*imb_count_sites_raccordes_optimum*/
(SELECT COUNT(imbs.dossier) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '11')),
/*imb_el_sites_raccordes_optimu*/
(SELECT SUM(nb_logements) FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = New.marche_id AND (SELECT cem_states.request FROM cem_states WHERE cem_states.cem_state_id = '11'))
FROM imbs LEFT JOIN optimum ON imbs.dossier = optimum.dossier WHERE imbs.marche_id = NEW.marche_id;
END;
END IF
merci pour votre aide