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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
| Create OR REPLACE trigger Liberer_emplacement
After Insert
On T_facture
Declare
Vnumfac T_facture.Num_facture%type;
Vasquitee T_facture.ACQUITTEE%type;
VNum_sejour T_sejour.Num_sejour%type;
Vnum_est T_sejour.num_est%type;
VCode_emp T_sejour.Code_emp%type:
Vnum_facture T_sejour.num_facture%type;
VDate_deb T_sejour.Date_deb%type;
VDate_fin T_sejour.Date_fin%type;
VMontant_sejour T_sejour.Montant_sejour%type;
VMontant_activite T_sejour.Montant_activite%type;
VNum_periode T_periode_sejour.Num_periode%type;
VNum_sejour T_periode_sejour.Num_sejour%type;
VNB_JOURS T_periode_sejour.NB_JOURS%type;
VNB_PERSONNES T_periode_sejour.NB_PERSONNES%type;
VNB_ENFANTS T_periode_sejour.NB_ENFANTS%type;
VNum_activite T_participer_activite.Num_activite%type;
VNum_sejour T_participer_activite.Num_sejour%type;
VNB_UNITE T_participer_activite.NB_UNITE%type;
VDATE_ACTIVITE T_participer_activite.DATE_ACTIVITE%type;
Cursor cursor1 is select Num_facture,ACQUITTEE from Inserted;
Cursor cursor2 is select Num_sejour,num_est, Code_emp,num_facture ,Date_deb ,Date_fin ,Montant_sejour,Montant_activite from T_sejour where num_facture = Vnumfac;
Cursor cursor3 is select Num_periode,Num_sejour,NB_JOURS,NB_PERSONNES,NB_ENFANTS from T_periode_sejour where Num_sejour= VNum_sejour;
Cursor cursor4 is select Num_activite,Num_sejour,NB_UNITE,DATE_ACTIVITE from T_participer_activite where Num_sejour= VNum_sejour;
Begin
Open cursor1;
fetch cursor1 into Vnumfac, Vasquitee;
if Vasquitee = false then
close cursor1;
open cursor2;
fetch cursor2 into VNum_sejour,Vnum_est,VCode_emp,Vnum_facture,VDate_deb,VDate_fin,VMontant_sejour,VMontant_activite;
close cursor2;
Insert into HT_sejour(Num_sejour,num_est,Code_emp,num_facture,Date_deb,Date_fin,Montant_sejour,Montant_activite) values (VNum_sejour,Vnum_est,VCode_emp,Vnum_facture,VDate_deb,VDate_fin,VMontant_sejour,VMontant_activite);
Delete from T_sejour where num_facture = Vnumfac;
open cursor3;
fetch cursor3 into VNum_periode,VNum_sejour,VNB_JOURS,VNB_PERSONNES,VNB_ENFANTS;
close cursor3;
Insert into hT_periode_sejour(Num_periode,Num_sejour,NB_JOURS,NB_PERSONNES,NB_ENFANTS) values (VNum_periode,VNum_sejour,VNB_JOURS,VNB_PERSONNES,VNB_ENFANTS);
Delete from T_periode_sejour where Num_sejour= VNum_sejour;
open cursor4;
fetch cursor4 into VNum_activite,VNum_sejour,VNB_UNITE,VDATE_ACTIVITE;
close cursor4;
Insert into HT_participer_activite(Num_activite,Num_sejour,NB_UNITE,DATE_ACTIVITE) values (VNum_activite,VNum_sejour,VNB_UNITE,VDATE_ACTIVITE);
Delete from T_participer_activite where Num_sejour= VNum_sejour;
end if;
end;
/ |