an example to create trigger


use db240399481;
ALTER TABLE document ADD nb_avis int(11) DEFAULT 0;
ALTER TABLE document ADD average_note FLOAT(10,3) DEFAULT 0;

UPDATE document SET nb_avis = (SELECT COUNT(id_avis) FROM avis WHERE avis.id_document = document.id_document AND avis.date_validation>0 GROUP BY document.id_document);
UPDATE document SET average_note=(SELECT coalesce(AVG(avis.note),0) FROM avis WHERE avis.id_document =document.id_document AND avis.date_validation>0 GROUP BY document.id_document);

DELIMITER |
CREATE TRIGGER avis_update_trig AFTER INSERT ON avis 
FOR EACH ROW
BEGIN
 IF EXISTS (SELECT 1 FROM document WHERE `id_document`=NEW.`id_document`) THEN
  UPDATE document SET `average_note`=(`average_note`*`nb_avis`+NEW.`note`)/(`nb_avis`+1)
  WHERE `id_document`=NEW.`id_document`;
  UPDATE document SET `nb_avis` =`nb_avis`+1
  WHERE `id_document`=NEW.`id_document`;
 END IF;
END
|
DELIMITER;
DELIMITER |
DROP TRIGGER avis_update_trig;

|
DELIMITER;

SHOW TRIGGERS;

INSERT INTO avis SET id_document=17, note=1000, id_membre=12,commentaire='abcdefg', date_creation=1213653738, cron_email_envoye=1;

评论

热门博文