SET TERM ^ ;

CREATE OR ALTER TRIGGER TTBMODELO_COMBINACAO_MOD_B01I FOR TBMODELO_COMBINACAO_MOD
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE COUNT_GRADES    INTEGER;
DECLARE VARIABLE GRADE_INCLUIDO  INTEGER;
DECLARE VARIABLE GRADE_EXISTENTE INTEGER;
DECLARE VARIABLE GRADE_DESCRICAO VARCHAR(200);
BEGIN

   FOR
    SELECT FIRST 1
       C.CODIGO AS GRADE_EXISTENTE,
       C.DESCRICAO AS GRADE_DESCRICAO
      FROM TBMODELO_COMBINACAO_MOD A, TBMODELO B, TBGRADE C
      WHERE A.COMBINACAO_ID = NEW.COMBINACAO_ID
      AND B.CODIGO = A.MODELO_ID
      AND C.CODIGO = B.GRADE_CODIGO
      AND C.CODIGO > 0
   INTO GRADE_EXISTENTE, GRADE_DESCRICAO DO
   BEGIN
       GRADE_INCLUIDO = COALESCE((SELECT FIRST 1 C.CODIGO FROM TBMODELO B, TBGRADE C WHERE B.CODIGO = NEW.MODELO_ID AND C.CODIGO = B.GRADE_CODIGO AND C.CODIGO > 0),0);

       IF(GRADE_INCLUIDO <> GRADE_EXISTENTE)THEN
       BEGIN
          EXCEPTION "..." 'MODELO '||COALESCE((SELECT FIRST 1 FN_LPAD(B.CODIGO,6,0)||' - '||B.DESCRICAO FROM TBMODELO B WHERE B.CODIGO = NEW.MODELO_ID),'')||' POSSUI GRADE DIFERENTE DOS MODELOS CADASTRADOS NESTA COMBINACAO ('||FN_LPAD(:GRADE_EXISTENTE,4,0)||' - '||:GRADE_DESCRICAO||').<BR> CORRIJA ISSO E TENTE NOVAMENTE.';
       END
   END

  COUNT_GRADES = COALESCE((SELECT COUNT (DISTINCT C.CODIGO) FROM TBMODELO_COMBINACAO_MOD A, TBMODELO B, TBGRADE C
                            WHERE A.COMBINACAO_ID = NEW.COMBINACAO_ID AND B.CODIGO = A.MODELO_ID AND C.CODIGO = B.GRADE_CODIGO AND A.ID IS NOT NULL),0);

  IF(COUNT_GRADES > 1)THEN
  BEGIN
     EXCEPTION "..." 'EXISTEM GRADES DIFERENTES NOS MODELOS DESTA COMBINACAO, CORRIJA ISTO E TENTE NOVAMENTE.';
  END

  IF (NEW.ID IS NULL) THEN
  BEGIN
    NEW.ID = GEN_ID(GTBMODELO_COMBINACAO_MOD,1);
  END
END^

SET TERM ; ^

