1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
| DROP TRIGGER IF EXISTS `trigger_MAJ_Relance`;
DELIMITER //
CREATE TRIGGER `trigger_MAJ_Relance` AFTER INSERT ON `tblRelanceProv`
FOR EACH ROW BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS Tempo (
RefClient varchar (20) default '',
RefFacture varchar (20) default '',
Utilisateur varchar (20) default '',
DateRelance datetime,
Commentaires varchar (100) default '',
DatePromessePayement datetime);
INSERT INTO Tempo (RefClient, RefFacture, Utilisateur, DateRelance, Commentaires, DatePromessePayement)
Select TFactures.RefClients, TFactures.RefFacture, tblRelanceProv.Utilisateur,
NOW() As DateRelance, tblRelanceProv.Commentaires, tblRelanceProv.DatePromise
From tblRelanceProv Inner Join
TFactures On TFactures.RefClients = tblRelanceProv.RefClient Left Join
TRelance On TRelance.RefFacture = TFactures.RefFacture
WHERE
( (TFactures.FactPaye = 0) AND (TRelance.DatePromessePayement < NOW()) OR (TFactures.FactPaye = 0) AND (TFactures.DateLimitePayement < NOW()) )
order by tblRelanceProv.DateRelance DESC LIMIT 1;
-- Insertion de la table temporaire dans la Table TRelance
INSERT INTO TRelance (TRelance.RefFacture, TRelance.Utilisateur, TRelance.DateRelance, TRelance.Commentaires, TRelance.DatePromessePayement)
select RefFacture, Utilisateur, DateRelance, Commentaires, DatePromessePayement
FROM Tempo;
DELETE FROM tblRelanceProv;
END
//
DELIMITER ; |
Partager