# Triggers para la base de datos elabo

DELIMITER //


#----------------------------------------------------------
# EXAMENES
#----------------------------------------------------------

#----------------------------------------------------------


DROP TRIGGER IF EXISTS elabo.examenes_bi;//
CREATE TRIGGER examenes_bi
BEFORE INSERT ON examenes
FOR EACH ROW
BEGIN
  DECLARE newc VARCHAR(5);
  DECLARE newn VARCHAR(30);
  DECLARE newp DOUBLE;
  DECLARE pruebatipo char(1);
  DECLARE num INTEGER;
  DECLARE dedu DOUBLE;
  DECLARE conv INTEGER;

  SET NEW.status = "E";
  SET NEW.imprime = 0;
  SET NEW.fechares = NOW();
  SET NEW.fechamod= NOW();	
  SET newc = NEW.codiex;
  SET newn = NEW.nombrexa;
  SET newp = NEW.precio;  
  SET num = NEW.numerex;

  CALL llenarexamen(newc, newn, newp, dedu, conv, pruebatipo);

  SET NEW.nombrexa = newn;
  SET NEW.convenio = conv;
  SET NEW.u = pruebatipo;
  IF pruebatipo = 'M' then
    SET NEW.precio = 0.00;
    SET NEW.deducible = 0.00;
  ELSE
    SET NEW.precio = newp;
    SET NEW.deducible = dedu;
  END IF;

END;//
#----------------------------------------------------------

#----------------------------------------------------------
DROP TRIGGER IF EXISTS elabo.examenes_ai;//
CREATE TRIGGER examenes_ai
AFTER INSERT ON examenes
FOR EACH ROW
BEGIN
  DECLARE num INTEGER;
  DECLARE codi CHAR(4);
  DECLARE exanum INTEGER;
  DECLARE exatipo CHAR(1);

  SET exatipo = NEW.u;
  SET num = NEW.numerex;
  SET codi = NEW.codiex;
  SET exanum = NEW.examenid;


  CALL inserte_impuestos(num, codi, exanum);	
  CALL calcule_total_orden(num);
  CALL inserte_resultados(num, codi, exanum);
  CALL status_orden(num);

END;//
#----------------------------------------------------------

#----------------------------------------------------------
DROP TRIGGER IF EXISTS elabo.examenes_bd;//
CREATE TRIGGER examenes_bd
BEFORE DELETE ON examenes
FOR EACH ROW
BEGIN
  DECLARE num INTEGER;

  SET num = OLD.examenid;

  CALL quite_impuestos(num);

END;//
#----------------------------------------------------------
#----------------------------------------------------------
DROP TRIGGER IF EXISTS elabo.examenes_ad;//
CREATE TRIGGER examenes_ad
AFTER DELETE ON examenes
FOR EACH ROW
BEGIN
  DECLARE num INTEGER;

  SET num = OLD.numerex;

  CALL calcule_total_orden(num);
  CALL status_orden(num);

END;//
#----------------------------------------------------------

#----------------------------------------------------------
DROP TRIGGER IF EXISTS elabo.examenes_au;//
CREATE TRIGGER examenes_au
AFTER UPDATE ON examenes
FOR EACH ROW
BEGIN
  DECLARE nume INTEGER;

  SET nume = OLD.numerex;

  CALL status_orden(nume);

  IF ((NEW.deducible <> OLD.deducible) or (NEW.precio <> OLD.precio)) then
	CALL calcule_total_orden(nume);
  END IF;
END;//
#----------------------------------------------------------

#---------------------------------------------------------
DELIMITER ;
