CREATE OR ALTER trigger ttbgp_etapa_pmp_coluna_b02i for tbgp_etapa_pmp_coluna
active before insert position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE GP_ID INTEGER;
DECLARE VARIABLE DESC_ETAPA VARCHAR(500);

BEGIN

    HISTORICO = '';
     SELECT FIRST 1 T.GP_ID FROM TBGP_ETAPA_PMP T WHERE T.ID = NEW.GP_ETAPA_PMP_ID INTO GP_ID;
     SELECT FIRST 1 T.DESCRICAO FROM TBGP_ETAPA_PMP T WHERE T.ID = NEW.GP_ETAPA_PMP_ID INTO DESC_ETAPA;

    IF ( NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'ETAPA '||NEW.GP_ETAPA_PMP_ID||' - '||DESC_ETAPA||' ALTERADA: '||' COLUNA INCLUIDA:'||NEW.ID||' - '||NEW.DESCRICAO;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBESTEIRA', GP_ID, HISTORICO);
    END
end;

CREATE trigger ttbgp_etapa_pmp_coluna_b01u for tbgp_etapa_pmp_coluna
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE GP_ID INTEGER;
DECLARE VARIABLE DESC_ETAPA VARCHAR(500);

BEGIN

    HISTORICO = '';
    SELECT FIRST 1 T.GP_ID FROM TBGP_ETAPA_PMP T WHERE T.ID = NEW.GP_ETAPA_PMP_ID INTO GP_ID;
    SELECT FIRST 1 T.DESCRICAO FROM TBGP_ETAPA_PMP T WHERE T.ID = NEW.GP_ETAPA_PMP_ID INTO DESC_ETAPA;

    IF ( OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' DESC: '||OLD.DESCRICAO||' -> '||NEW.DESCRICAO;
    END

    IF ( OLD.TIPO_CAMPO IS DISTINCT FROM NEW.TIPO_CAMPO) THEN
    BEGIN
        HISTORICO = HISTORICO||' TP.CAMPO: '||COALESCE(OLD.TIPO_CAMPO, 0)||' -> '||COALESCE(NEW.TIPO_CAMPO, 0);
    END

    IF ( OLD.FORMULA <> NEW.FORMULA) THEN
    BEGIN
        HISTORICO = HISTORICO||' FORMULA: '|| OLD.FORMULA||' -> '||NEW.FORMULA;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = ' ETAPA '||NEW.GP_ETAPA_PMP_ID||' - '||DESC_ETAPA||' ALTERADA: '||' COLUNA: '||NEW.ID||' ALTERADA: '||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBESTEIRA',GP_ID, HISTORICO);
    END
END;

CREATE trigger ttbgp_etapa_pmp_coluna_b01d for tbgp_etapa_pmp_coluna
active before delete position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE GP_ID INTEGER;
DECLARE VARIABLE DESC_ETAPA VARCHAR(500);

BEGIN

    HISTORICO = '';
     SELECT FIRST 1 T.GP_ID FROM TBGP_ETAPA_PMP T WHERE T.ID = OLD.GP_ETAPA_PMP_ID INTO GP_ID;
     SELECT FIRST 1 T.DESCRICAO FROM TBGP_ETAPA_PMP T WHERE T.ID = OLD.GP_ETAPA_PMP_ID INTO DESC_ETAPA;

    IF ( OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'ETAPA '||OLD.GP_ETAPA_PMP_ID||' - '||DESC_ETAPA||' ALTERADA: '||' COLUNA EXCLUIDA:'||OLD.ID||' - '||OLD.DESCRICAO;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBESTEIRA', GP_ID, HISTORICO);
    END
end;



