CREATE TABLE TBSCRIPT_IMP (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(50),
    STATUS INTEGER,
    SQL BLOB SUB_TYPE 1 SEGMENT SIZE 80);

ALTER TABLE TBSCRIPT_IMP
ADD CONSTRAINT PK_TBSCRIPT_IMP
PRIMARY KEY (ID);
	
CREATE TABLE TBSCRIPT_IMP_VARIAVEIS (
    ID INTEGER NOT NULL,
    SCRIPT_ID INTEGER NOT NULL,
    DESCRICAO_ITEM VARCHAR(50),
    TIPO INTEGER NOT NULL);

ALTER TABLE TBSCRIPT_IMP_VARIAVEIS
ADD CONSTRAINT PK_TBSCRIPT_IMP_VARIAVEIS
PRIMARY KEY (ID);

CREATE SEQUENCE GTBSCRIPT_IMP;
SET GENERATOR GTBSCRIPT_IMP TO 1;

CREATE SEQUENCE GTBSCRIPT_IMP_VARIAVEIS;
SET GENERATOR GTBSCRIPT_IMP_VARIAVEIS TO 1;

CREATE TRIGGER TTBSCRIPT_IMP_B01I FOR TBSCRIPT_IMP
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GTBSCRIPT_IMP,1);
END;

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

CREATE TRIGGER TTBSCRIPT_IMP_B02I FOR TBSCRIPT_IMP
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('TBSCRIPT_IMP',NEW.ID, HISTORICO);
    END
end;

CREATE TRIGGER TTBSCRIPT_IMP_B01U FOR TBSCRIPT_IMP
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

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

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

CREATE TRIGGER TTBSCRIPT_IMP_B01D FOR TBSCRIPT_IMP
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBSCRIPT_IMP',old.ID, HISTORICO);
    END
end;

CREATE TRIGGER TTBSCRIPT_IMP_VAR_B02I FOR TBSCRIPT_IMP_VARIAVEIS
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'Item Incluido:'||new.ID;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBSCRIPT_IMP',(SELECT T.ID FROM TBSCRIPT_IMP T WHERE ID = NEW.SCRIPT_ID), HISTORICO);
    END
end;

CREATE TRIGGER TTBSCRIPT_IMP_VAR_B01U FOR TBSCRIPT_IMP_VARIAVEIS
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBSCRIPT_IMP',(SELECT T.ID FROM TBSCRIPT_IMP T WHERE ID = NEW.SCRIPT_ID),'Item:'||OLD.ID||' '||HISTORICO);
    END
end;

CREATE TRIGGER TTBSCRIPT_IMP_VAR_B01D FOR TBSCRIPT_IMP_VARIAVEIS
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'Item Excluido:'||OLD.ID;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBSCRIPT_IMP',(SELECT T.ID FROM TBSCRIPT_IMP T WHERE ID = OLD.SCRIPT_ID), HISTORICO);
    END
end;




