CREATE OR REPLACE TRIGGER SUPR_MAT BEFORE DELETE ON MODULE FOR EACH ROW
DECLARE
enseigne NUMBER (4,0);
BEGIN
SELECT COUNT(*) INTO enseigne  FROM ENSEIGNT WHERE ENSEIGNT.CODE = :old.CODE;
IF enseigne > 0 THEN
 RAISE_APPLICATION_ERROR(-20000, 'Erreur : en cours d enseignement');
END IF;
END;

CREATE OR REPLACE TRIGGER CREA_MAT BEFORE INSERT ON MODULE FOR EACH ROW
DECLARE
enseigne NUMBER (4,0);
BEGIN
SELECT COUNT(*) INTO enseigne  FROM ENSEIGNT WHERE ENSEIGNT.CODE = :new.CODE;
IF enseigne > 0 THEN
 RAISE_APPLICATION_ERROR(-20001, 'Erreur : en cours d enseignement');
END IF;
END;

DELETE FROM MODULE WHERE CODE = 'BD';
INSERT INTO MODULE VALUES ('baba');

CREATE OR REPLACE TRIGGER UpNote BEFORE INSERT OR UPDATE ON NOTATION
BEGIN
IF :new.MOY_CC IS NULL THEN
  :new.MOY_CC := 0;
  END IF;
IF :new.MOY_TEST IS NULL THEN
  :new.MOY_TEST := 0;
END IF;
END;

create or replace trigger ajnote before insert or update on notation
for each row
declare
nb number (4,0);
begin
select count(*) into nb from enseignt where code = :new.code and num_et = :new.num_et;
if nb = 0 then
 RAISE_APPLICATION_ERROR(-20002, 'pas enseigne');
end if;
end;

create or replace trigger coefc before insert or update on module
for each row
declare
tot number(4,0);
begin
tot := :new.coeff_cc + :new.coeff_test;
if tot != 100 then
 RAISE_APPLICATION_ERROR(-20002, 'pas 100');
end if;
end;

CREATE TABLE GROUPE (ANNEE NUMBER(2,0), NUMERO NUMBER(1,0), EFFECTIF NUMBER(3,0));
DROP TABLE GROUPE;
DECLARE
cursor grps is select groupe, annee, count(*) compt from etudiant group by groupe, annee;
BEGIN
for it in grps
loop
  insert into groupe values (it.annee, it.groupe, it.compt);
end loop;
END;

SELECT * from groupe;


CREATE OR REPLACE TRIGGER upetu before insert or update or delete on etudiant
FOR EACH ROW
BEGIN
  if :new.groupe = null then
    select numero into :new.groupe from groupe where effectif in (
      select min(effectif) from groupe);
  end if;
  if inserting then
   UPDATE groupe set effectif = effectif + 1 where numero = :new.groupe;
   end if;
  if updating then
   UPDATE groupe set effectif = effectif + 1 where numero = :new.groupe;
   UPDATE groupe set effectif = effectif - 1 where numero = :old.groupe;
  end if;
  if deleting then
     UPDATE groupe set effectif = effectif - 1 where numero = :old.groupe;
  end if;
END;