trig03.sql

-- ----------------------------------------------
-- @Author: bendem
-- @Date:   2013-12-16 11:28:14
-- @Last Modified by:   bendem
-- @Last Modified time: 2013-12-16 12:01:35
-- ----------------------------------------------

CREATE OR REPLACE TRIGGER NageursCategorie
BEFORE INSERT OR UPDATE ON Nageurs
FOR EACH ROW
DECLARE
	age NUMBER;
BEGIN
	SELECT AnneeEnCours - :new.AnneeNaiss INTO age FROM InfoGenerales;
	SELECT categorie INTO :new.categorie FROM categories, InfoGenerales WHERE
		-- age BETWEEN AgeMin AND AgeMax?
		age >= AgeMin
		AND age < AgeMax;
EXCEPTION
	WHEN OTHERS THEN
		raise;
END;
/

-- -----------------------------------------------------
--  OU (FAUX, le déclencheurs va tourner en boucle...!)
--              Erreur de table mutante!
-- -----------------------------------------------------
-- CREATE OR REPLACE TRIGGER NageursCategorie
-- AFTER INSERT OR UPDATE ON Nageurs
-- FOR EACH ROW
-- BEGIN
-- 	UPDATE Nageurs SET categorie = (
-- 		SELECT categorie FROM categories, InfoGenerales
-- 		WHERE
-- 			AnneeEnCours - AnneeNaiss >= AgeMin
-- 			AND AnneeEnCours - AnneeNaiss < AgeMax
-- 	)
-- 	WHERE nrligue = :new.nrligue;
-- EXCEPTION
-- 	WHEN OTHERS THEN
-- 		raise;
-- END;
-- /