ALTER TABLE TBCOLABORADOR_OCORRENCIA
ADD DATA_OCORRENCIA DATE
DEFAULT 'NOW';

UPDATE TBCOLABORADOR_OCORRENCIA SET DATA_OCORRENCIA = CAST(DATAHORA AS DATE);

CREATE OR ALTER TRIGGER TTBCOLABORADOR_OCORRENCIA_B01I FOR TBCOLABORADOR_OCORRENCIA
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GTBCOLABORADOR_OCORRENCIA,1);
END;

CREATE OR ALTER TRIGGER TTBCOLABORADOR_OCORRENCIA_B02I FOR TBCOLABORADOR_OCORRENCIA
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'REGISTRO INCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBCOLABORADOR_OCORRENCIA',NEW.ID, HISTORICO);
    END
END;

CREATE OR ALTER TRIGGER TTBCOLABORADOR_OCORRENCIA_B01U FOR TBCOLABORADOR_OCORRENCIA
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( OLD.DATA_OCORRENCIA <> NEW.DATA_OCORRENCIA) THEN
    BEGIN
        HISTORICO = HISTORICO||' DATA:'||FN_DATE(OLD.DATA_OCORRENCIA,'DD/MM/YYYY')||'->'||FN_DATE(NEW.DATA_OCORRENCIA,'DD/MM/YYYY');
    END

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

    IF ( OLD.OCORRENCIA <> NEW.OCORRENCIA) THEN
    BEGIN
        HISTORICO = HISTORICO||' OBS:'|| OLD.OCORRENCIA||'->'||NEW.OCORRENCIA;
    END
	
    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBCOLABORADOR_OCORRENCIA',NEW.ID, HISTORICO);
    END
END;

CREATE OR ALTER TRIGGER TTBCOLABORADOR_OCORRENCIA_B01D FOR TBCOLABORADOR_OCORRENCIA
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'REGISTRO EXCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBCOLABORADOR_OCORRENCIA',OLD.ID, HISTORICO);
    END
END;