CREATE TABLE TBEMAIL_FORMULARIO (
    ID INTEGER NOT NULL,
    FORMULARIO_ID INTEGER,
    EMAIL BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    ASSUNTO VARCHAR(100),
    STATUS INTEGER,
    DESCRICAO VARCHAR(100));

ALTER TABLE TBEMAIL_FORMULARIO
ADD CONSTRAINT PK_TBEMAIL_FORMULARIO
PRIMARY KEY (ID);

COMMENT ON COLUMN TBEMAIL_FORMULARIO.STATUS IS
'0 - inativo
1 - ativo';

CREATE SEQUENCE GTBEMAIL_FORMULARIO;

create trigger ttbemail_formulario_b01i for tbemail_formulario
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbemail_formulario,1);
end;

CREATE trigger ttbemail_formulario_b02i for tbemail_formulario
active before insert position 2
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   HISTORICO = 'Registro Incluído';

   if (HISTORICO <> '') Then
   Begin
      execute procedure SPI_HISTORICO('TBEMAIL_FORMULARIO', new.id, :HISTORICO);
   End
end;

CREATE trigger ttbemail_formulario_b01u for tbemail_formulario
active before update position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   HISTORICO = '';

   if(new.formulario_id <> old.formulario_id)then
   begin
       HISTORICO = HISTORICO || ', Formulário:'||old.formulario_id||' -> '||new.formulario_id;
   end

   if(new.status <> old.status)then
   begin
       HISTORICO = HISTORICO || ', Status:'||old.status||' -> '||new.status;
   end

   if(new.descricao <> old.descricao)then
   begin
       HISTORICO = HISTORICO || ', Descrição:'||old.descricao||' -> '||new.descricao;
   end

   if(new.assunto <> old.assunto)then
   begin
       HISTORICO = HISTORICO || ', Assunto:'||old.assunto||' -> '||new.assunto;
   end

   if(new.email <> old.email)then
   begin
       HISTORICO = HISTORICO || ', Email alterado';
   end

   if (HISTORICO <> '') Then
   Begin
      execute procedure SPI_HISTORICO('TBEMAIL_FORMULARIO', new.id, :HISTORICO);
   End
end;

CREATE trigger ttbemail_formulario_b01d for tbemail_formulario
active before delete position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
begin

   HISTORICO = 'Registro Excluido';

   if (HISTORICO <> '') Then
   Begin
      execute procedure SPI_HISTORICO('TBEMAIL_FORMULARIO', old.id, :HISTORICO);
   End
end;


