CREATE OR ALTER TRIGGER TTBEAN_B05IU FOR TBEAN
ACTIVE BEFORE INSERT OR UPDATE POSITION 1
AS
DECLARE VARIABLE EAN               VARCHAR(50);
DECLARE VARIABLE TAMANHO_DESCRICAO VARCHAR(50);
DECLARE VARIABLE COR_DESCRICAO     VARCHAR(200);
DECLARE VARIABLE MODELO_DESCRICAO  VARCHAR(200);
BEGIN
  EAN               = '';
  TAMANHO_DESCRICAO = '';
  COR_DESCRICAO     = '';
  MODELO_DESCRICAO  = '';
  IF(INSERTING)THEN
  BEGIN
      FOR
       SELECT FIRST 1 A.EAN,
            (SELECT FIRST 1 FN_LPAD(B.CODIGO,4,0)||' - '||B.DESCRICAO FROM TBCOR    B WHERE B.CODIGO = A.COR_ID)    AS COR_DESCRICAO,
            (SELECT FIRST 1 FN_LPAD(C.CODIGO,6,0)||' - '||C.DESCRICAO FROM TBMODELO C WHERE C.CODIGO = A.MODELO_ID) AS MODELO_DESCRICAO,
            FN_TAMANHO_GRADE((SELECT FIRST 1 D.GRADE_CODIGO FROM TBPRODUTO D WHERE D.COR_CODIGO = A.COR_ID AND D.MODELO_CODIGO = A.MODELO_ID),A.TAMANHO) AS TAMANHO_DESCRICAO

            FROM TBEAN A
            WHERE A.MODELO_ID <> NEW.MODELO_ID
              AND A.COR_ID    <> NEW.COR_ID
              AND A.PREFIXO    = NEW.PREFIXO
              AND A.CODIGO     = NEW.CODIGO

            INTO EAN, COR_DESCRICAO, MODELO_DESCRICAO, TAMANHO_DESCRICAO DO
            BEGIN
               EXCEPTION "..." 'EAN:'||COALESCE(NEW.PREFIXO||NEW.CODIGO,'')||' JA CADASTRADO. <BR> MODELO:'||COALESCE(MODELO_DESCRICAO,'')||'.<BR> COR:'||COALESCE(COR_DESCRICAO,'')||'.<BR> TAM:'||COALESCE(TAMANHO_DESCRICAO,'')||'.<BR> OPERAÇÃO CANCELADA!';
            END
  END

  IF(UPDATING)THEN
  BEGIN
      IF(OLD.CODIGO <> NEW.CODIGO OR OLD.PREFIXO <> NEW.PREFIXO)THEN
      BEGIN
       FOR
         SELECT FIRST 1 A.EAN,
            (SELECT FIRST 1 FN_LPAD(B.CODIGO,4,0)||' - '||B.DESCRICAO FROM TBCOR    B WHERE B.CODIGO = A.COR_ID)    AS COR_DESCRICAO,
            (SELECT FIRST 1 FN_LPAD(C.CODIGO,6,0)||' - '||C.DESCRICAO FROM TBMODELO C WHERE C.CODIGO = A.MODELO_ID) AS MODELO_DESCRICAO,
            FN_TAMANHO_GRADE((SELECT FIRST 1 D.GRADE_CODIGO FROM TBPRODUTO D WHERE D.COR_CODIGO = A.COR_ID AND D.MODELO_CODIGO = A.MODELO_ID),A.TAMANHO) AS TAMANHO_DESCRICAO

            FROM TBEAN A
            WHERE A.MODELO_ID <> NEW.MODELO_ID
              AND A.COR_ID    <> NEW.COR_ID
              AND A.PREFIXO    = NEW.PREFIXO
              AND A.CODIGO     = NEW.CODIGO

            INTO EAN, COR_DESCRICAO, MODELO_DESCRICAO, TAMANHO_DESCRICAO DO
            BEGIN
               EXCEPTION "..." 'EAN:'||COALESCE(NEW.PREFIXO||NEW.CODIGO,'')||' JA CADASTRADO. <BR> MODELO:'||COALESCE(MODELO_DESCRICAO,'')||'.<BR> COR:'||COALESCE(COR_DESCRICAO,'')||'.<BR> TAM:'||COALESCE(TAMANHO_DESCRICAO,'')||'.<BR> OPERAÇÃO CANCELADA!';
            END
       END
  END

END;