CREATE OR REPLACE FUNCTION majstatAchat() RETURNS TRIGGER AS $$
DECLARE
codif TEXT;
id_mois TEXT;
BEGIN
SELECT INTO codif,id_mois lower(substr(numFact, 1,4)), substr(dateFacture, 6,2)
FROM FACTURER F1
INNER JOIN FACTURES F2 ON F1.idFacture=F2.idFacture
WHERE idVO=NEW.idVO;
IF codif IS NULL THEN
RETURN NULL;
ELSE
IF (TG_OP = 'UPDATE') THEN
UPDATE stat_achat_mois SET fa07=(fa07 - OLD.prixachat + NEW.prixachat) WHERE idmois = '02';
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
UPDATE stat_achat_mois SET fa07=(fa07 + NEW.prixachat) WHERE idmois = '02';
RETURN NEW;
END IF;
END IF;
END;
$$ language plpgsql;
CREATE TRIGGER trig_majstatAchat
AFTER INSERT OR UPDATE ON PRODUITS
FOR EACH ROW EXECUTE PROCEDURE majstatAchat();
Partager