CREATE TABLE TBROYALTIES (
    ID INTEGER NOT NULL,
    DESCRICAO VARCHAR(100),
    CANAL_VENDAS_ID INTEGER,
    DATA_INICIAL DATE,
    DATA_FINAL DATE);

ALTER TABLE TBROYALTIES
ADD CONSTRAINT PK_TBROYALTIES
PRIMARY KEY (ID);

CREATE INDEX ITBROYALTIES_ID01
ON TBROYALTIES (CANAL_VENDAS_ID);

CREATE SEQUENCE GTBROYALTIES;

SET TERM ^ ;

create trigger ttbroyalties_b01i for tbroyalties
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbroyalties,1);
end
^

SET TERM ; ^


SET TERM ^ ;

CREATE trigger ttbroyalties_b02i for tbroyalties
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('TBROYALTIES', NEW.ID, HISTORICO);
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE trigger ttbroyalties_b09u for tbroyalties
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

    HISTORICO = '';

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

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

    IF (NEW.DATA_INICIAL <> OLD.DATA_INICIAL) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT.INI: '||FORMATDATE(OLD.DATA_INICIAL)||' -> '||FORMATDATE(NEW.DATA_INICIAL);
    END
    
    IF (NEW.DATA_FINAL <> OLD.DATA_FINAL) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT.FIM: '||FORMATDATE(OLD.DATA_FINAL)||' -> '||FORMATDATE(NEW.DATA_FINAL);
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBROYALTIES', NEW.ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE trigger ttbroyalties_b01d for tbroyalties
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('TBROYALTIES', OLD.ID, HISTORICO);
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbroyalties_b02d for tbroyalties
active before delete position 0
AS
begin

    IF(OLD.ID > 0) THEN
    BEGIN
        DELETE FROM TBROYALTIES_CLIENTE WHERE ROYALTIES_ID = OLD.ID;
        DELETE FROM TBROYALTIES_MODELO WHERE ROYALTIES_ID = OLD.ID;
    END

end
^

SET TERM ; ^