TRIGGER_BODY |
DECLARE
BEGIN
UPDATE etudiants_absences
SET justification = 'anj'
WHERE pf_groupe = :new.f_groupe
AND (SELECT e1.jour FROM edt e1 WHERE e1.p_id = pf_edt)+1 BETWEEN to_char(:new.date_debut, 'D') AND to_char(:new.date_fin, 'D')
AND (SELECT e1.semaine FROM edt e1 WHERE e1.p_id = pf_edt) BETWEEN to_char(:new.date_debut, 'WW') AND to_char(:new.date_fin, 'WW')
AND (SELECT COUNT(*)
FROM absences_justifiees aj
WHERE aj.f_groupe = :new.f_groupe
AND (SELECT e2.jour FROM edt e2 WHERE e2.p_id = pf_edt)+1 BETWEEN to_char(aj.date_debut, 'D') AND to_char(aj.date_fin, 'D')
AND (SELECT e2.semaine FROM edt e2 WHERE e2.p_id = pf_edt) BETWEEN to_char(aj.date_debut, 'WW') AND to_char(aj.date_fin, 'WW')) = 0;
END; |