Événement sur une table
cid5420
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 IFmerci pour votre aide