CREATE OR REPLACE PROCEDURE informarFallo (
  laPieza IN NUMBER,
  elProv IN NUMBER,
  descr IN VARCHAR)
IS
  numTuplas NUMBER;
BEGIN
  INSERT INTO informesFallos VALUES (idInformeSQ.NEXTVAL, laPieza, elProv, SYSDATE, USER, descr);
  SELECT count(*) INTO numTuplas FROM totalFallos WHERE clvPieza = laPieza AND clvProv = elProv;
  IF numTuplas > 0 THEN
    UPDATE totalFallos SET numFallos = numFallos + 1 WHERE clvPieza = laPieza AND clvProv = elProv;
  ELSE
    INSERT INTO totalFallos VALUES (laPieza, elProv, 1);
  END IF;
-- más cosas ligadas al fallo
  COMMIT;  
END;
/

CREATE OR REPLACE PROCEDURE actualizarTotalFallos
IS
  numTuplas NUMBER;
BEGIN
  DELETE FROM totalFallos;

  INSERT INTO totalFallos
    SELECT clvPieza, clvProv, count(*) FROM informesFallos  GROUP BY clvPieza, clvProv;

  DELETE FROM sumNoValidos;

  INSERT INTO sumNoValidos
    SELECT clvPieza, clvProv FROM totalFallos  WHERE numFallos > 2;

  COMMIT;  
END;
/

DROP TRIGGER testMaxFallos;

-- en esta versión da problema de tabla mutante
CREATE OR REPLACE TRIGGER testMaxFallos
AFTER INSERT OR UPDATE OF clvPieza, clvProv
ON informesFallos
FOR EACH ROW

DECLARE
  totFallos NUMBER;
BEGIN
  SELECT count(*) INTO totFallos
  FROM informesFallos
  WHERE :NEW.clvPieza = clvPieza AND :NEW.clvProv = clvProv;
  
  IF totFallos >= 3
   THEN
    INSERT INTO sumNoValidos VALUES(:NEW.clvPieza, :NEW.clvProv);
  END IF;
END testMaxFallos;
/


CREATE OR REPLACE TRIGGER testMaxFallos
AFTER INSERT OR UPDATE OF clvPieza, clvProv
ON informesFallos
FOR EACH ROW

DECLARE
  totFallos NUMBER;
BEGIN
  SELECT numFallos INTO totFallos
   FROM totalFallos
   WHERE :NEW.clvPieza = clvPieza AND :NEW.clvProv = clvProv;

  IF totFallos >=3
   THEN INSERT INTO sumNoValidos VALUES(:NEW.clvPieza, :NEW.clvProv);
   ELSE DELETE FROM sumNoValidos WHERE clvPieza = :NEW.clvPieza AND clvProv = :NEW.clvProv;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN NULL;
  WHEN OTHERS THEN  NULL;
END testMaxFallos;
/

CREATE OR REPLACE TRIGGER testMaxFallos_AI
AFTER INSERT OR UPDATE OF clvPieza, clvProv
ON informesFallos
FOR EACH ROW

DECLARE
  numTuplas NUMBER;
BEGIN
  SELECT count(*) INTO numTuplas FROM totalFallos WHERE clvPieza = :NEW.clvPieza AND clvProv = :NEW.clvProv;
  IF numTuplas > 0 THEN
    UPDATE totalFallos SET numFallos = numTuplas + 1 WHERE clvPieza = :NEW.clvPieza AND clvProv = :NEW.clvProv;
  ELSE
    INSERT INTO totalFallos VALUES (:NEW.clvPieza, :NEW.clvProv, 1);
  END IF;
END testMaxFallos;
/

CREATE OR REPLACE TRIGGER informeFallo_BI
BEFORE INSERT ON informesFallos
FOR EACH ROW
BEGIN
-- en Oracle 11g se permite hacer directamente:
--   :NEW.idInforme := idInformeSQ.NEXTVAL;
  SELECT idInformeSQ.NEXTVAL INTO :NEW.idInforme FROM dual;
  :NEW.fecha     := SYSDATE;
  :NEW.empleado  := USER;
END informeFallo_BI;
/


CREATE OR REPLACE TRIGGER testSuministro_BI
BEFORE INSERT ON suministrar
FOR EACH ROW
DECLARE
  numTuplas NUMBER;
BEGIN
  SELECT count(*) INTO numTuplas FROM sumNoValidos WHERE clvPieza = :NEW.clvPieza AND clvProv = :NEW.clvProv;
  IF numTuplas > 0 THEN
    raise_application_error( -20502, 'el vendedor '||:NEW.clvProv||' no puede suministrar la pieza '||:NEW.clvPieza);
  END IF;
END testSuministro_BI;
/

-- si se define para completar la insercion el trigger informeFallo_BI
INSERT INTO informesFallos (clvPieza, clvProv, descr)
 VALUES (94, 96, 'fallo recurrente raro');
 
DELETE FROM informesFallos WHERE idInforme = 4;

CREATE SEQUENCE idInformeSQ;

DROP SEQUENCE idInformeSQ;