CREATE TABLE TBCHAT (
  ID INTEGER  NOT NULL,
  REMETENTE_ID INTEGER ,
  DESTINATARIO_ID INTEGER ,
  MENSAGEM BLOB SEGMENT SIZE 100,
  DATAHORA TIMESTAMP DEFAULT 'NOW',
  STATUSEXCLUSAO CHAR(1) DEFAULT '0',
  DATAHORA_INSERT TIMESTAMP DEFAULT 'NOW',
  DATAHORA_UPDATE TIMESTAMP ,
  DATAHORA_DELETE TIMESTAMP );

ALTER TABLE TBCHAT ADD PRIMARY KEY (ID);

CREATE INDEX ITBCHAT_01 ON TBCHAT(STATUSEXCLUSAO);
CREATE INDEX ITBCHAT_02 ON TBCHAT(REMETENTE_ID);
CREATE INDEX ITBCHAT_03 ON TBCHAT(DESTINATARIO_ID);

CREATE GENERATOR GTBCHAT;


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

COMMENT ON TRIGGER TTBCHAT_B0I IS 'Gerar ID automaticamente.';


CREATE TRIGGER TTBCHAT_B0U FOR TBCHAT
ACTIVE BEFORE 
  UPDATE
POSITION 0
AS
/**************************************************
   ATUALIZA OS CAMPOS DATAHORA UPDATE E DELETE E
   REGISTRA HISTORICO DE EXCLUSAO/RECUPERACAO
**************************************************/
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    /* UPDATE */
    IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
    END
    /* DELETE */
    ELSE IF ( NEW.STATUSEXCLUSAO = '1' AND OLD.STATUSEXCLUSAO = '0' ) THEN
    BEGIN
        NEW.DATAHORA_DELETE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. EXCLUIDO.';
    END
    /* RECUPERAR */
    ELSE IF ( NEW.STATUSEXCLUSAO = '0' AND OLD.STATUSEXCLUSAO = '1' ) THEN
    BEGIN
        NEW.DATAHORA_UPDATE = CURRENT_TIMESTAMP;
        HISTORICO = 'REG. RECUPERADO.';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        INSERT INTO TBHISTORICO (
            TABELA,
            TABELA_ID,
            USUARIO,
            IP,
            HISTORICO
        ) VALUES (
            'TBCHAT',
            NEW.ID,
            (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
            :HISTORICO
        );
    END
END;

COMMENT ON TRIGGER TTBCHAT_B0U IS 'Atualiza os campos DATAHORA update e delete e registra historico de exclusao/recuperacao.';