Forums PostgreSQL.fr

CREATE OR REPLACE FUNCTION update_t_suivi() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO dashboard.t_suivi  ( 
  id_coord, 
  lot, 
  cdd,
  mission_moe,
  id_prog, 
  id_nro, 
  numero,
  insee, 
  com_dep, 
  statut, 
  emprise, 
  nom, 
  travaux, 
  longueur, 
  debut_trvx, 
  prog_dsp, 
  moa, 
  commentair, 
  envoi_moe
)
VALUES(
 new.id_opp, 
  new.lot, 
  new.cdd,
  new.phase,
  new.id_prog, 
  new.id_nro,  
  new.numero,
  new.insee, 
  new.com_dep, 
  new.statut, 
  new.emprise, 
  new.nom, 
  new.travaux, 
  new.longueur, 
  new.debut_trvx, 
  new.prog_dsp, 
  new.moa, 
  new.commentair, 
  new.envoi_moe)
;
  RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';



DROP TRIGGER IF EXISTS trg_update_t_suivi_num ON dashboard.t_suivi;
DROP TRIGGER IF EXISTS trg_update_t_suivi_num ON coordination.numerisation;
CREATE TRIGGER trg_update_t_suivi_num
AFTER UPDATE OR INSERT ON coordination.numerisation
FOR EACH ROW
EXECUTE PROCEDURE update_t_suivi();

CREATE OR REPLACE FUNCTION delete_t_suivi() RETURNS TRIGGER AS $$
BEGIN
 WITH a_supprimer AS
(
SELECT *
FROM dashboard.t_suivi AS num
WHERE num.id_coord  NOT IN  
(SELECT DISTINCT id_opp
FROM coordination.numerisation  opp
WHERE id_coord=id_opp
GROUP BY id_coord, id_opp
))
DELETE FROM dashboard.t_suivi a
USING a_supprimer b
WHERE a.id = b.id;

  RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';



DROP TRIGGER IF EXISTS trg_delete_t_suivi_num ON dashboard.t_suivi;
DROP TRIGGER IF EXISTS trg_delete_t_suivi_num ON coordination.numerisation;
CREATE TRIGGER trg_delete_t_suivi_num
AFTER UPDATE OR DELETE ON coordination.numerisation 
FOR EACH ROW
EXECUTE PROCEDURE delete_t_suivi();