Criação de uma Trigger[RESOLVIDO]

Assuntos Gerais
#1

Olá,

Estou desenvolvendo uma aplicação pequena, desktop, que persiste dados em um banco.
Como são apenas 7 tabelas, e um usuário, resolvi usar um banco de dados embutido, no caso, HSQLDB.
Tenho 2 tabelas do banco, dessa forma:

CREATE TABLE DIARIAS
(
    ID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL,
    EMPENHO INTEGER CHECK(EMPENHO >= 1),
    DATA_DIARIA TIMESTAMP NOT NULL,
    FK_FUNCIONARIO_ID INTEGER NOT NULL,
    HORARIO_SAIDA TIMESTAMP NOT NULL,
    HORARIO_CHEGADA TIMESTAMP NOT NULL,
    ... outros dados não importantes no contexto ...
);
CREATE TABLE DOCUMENTOS_FISCAIS
(
    ID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL,
    FORNECEDOR VARCHAR(100) NOT NULL,
    VALOR DECIMAL(10,2),
    DATA_DOCUMENTO TIMESTAMP NOT NULL,
    FK_DIARIA_ID INTEGER NOT NULL,
    CONSTRAINT PK_NOTAS_FISCAIS PRIMARY KEY(ID),
    CONSTRAINT FK_NFS_DIARIAS FOREIGN KEY(FK_DIARIA_ID) REFERENCES DIARIAS(ID) ON UPDATE CASCADE ON DELETE RESTRICT
);

Como vcs podem ver, a tabela DOCUMENTOS_FISCAIS está vinculada a tabela DIARIAS, em um relacionamento Muitos-para-um.
O que eu gostaria de fazer é criar uma Trigger SQL, que fizesse o seguinte:
Ao se inserir ou atualizar um registro em DOCUMENTOS_FISCAIS, a data dele (DATA_DOCUMENTO), deve estar entre a data de saída (HORARIO_SAIDA) e chegada (HORARIO_CHEGADA) do registro da tabela DIARIAS no qual ele está vinculado. Se estiver, o documento fiscal é persistido. Se não, sei lá, dispara uma Exception ou dá um rollback.

Acho que seria uma coisa mais ou menos assim, mas dá um erro e não compila:

CREATE TRIGGER VERIFICA_DATA_DF BEFORE INSERT ON DOCUMENTOS_FISCAIS
REFERENCING NEW AS NEW FOR EACH ROW
BEGIN ATOMIC
    DECLARE HORARIO_SAIDA TIMESTAMP;
    DECLARE HORARIO_CHEGADA TIMESTAMP;

    -- acho que o problema é aqui.....
    SET HORARIO_SAIDA = (SELECT DATA_DIARIA FROM DIARIAS JOIN DOCUMENTOS_FISCAIS ON DIARIAS.ID = NEW.FK_DIARIA_ID);
    SET HORARIO_CHEGADA = (SELECT DATA_DIARIA FROM DIARIAS JOIN DOCUMENTOS_FISCAIS ON DIARIAS.ID = NEW.FK_DIARIA_ID);

    IF NEW.DATA_DOCUMENTO < HORARIO_SAIDA OR NEW.DATA_DOCUMENTO > HORARIO_CHEGADA THEN
        -- gera uma exception, ou dá um rollback
    END IF;
END

Até entendo de SQL, mas esse tipo de consulta, em triggers, nunca fiz…

Então, podem me ajudar?
Agradeço a todos de boa vontade.
Thx :smiley:

#2

Onde dá o erro?
Qual o erro?

#3

java.sql.SQLException: user lacks privilege or object not found: NEW.FK_DIARIA_ID;

Esse erro dá no programinha que vem com o HSQLDB, o Database Manager Swing.

É so isso que diz, não diz a linha ou lugar do código…

#4

Dê uma olhada na permissão do usuário que está acessando o banco.
Esse erro é meio genérico, fala da permissão ou que aquela coluna não existe.

#5

Olhei a documentação do HSQLDB e me parece que seu problema está aqui:

troque por:

#6

Olá,

Ademilton, aquela linha estava certa, já tinha feito 2 outras triggers funcionais daquela forma. As duas formas estão corretas.
drsmachado, a permissão do usuário era de admin, e a coluna existe.

Mas eu consegui resolver o problema, a trigger ficou dessa forma (acho que o código ficou bem auto-explicativo):

CREATE TRIGGER VERIFICA_DATA_NF BEFORE INSERT ON DOCUMENTOS_FISCAIS
REFERENCING NEW AS NEW FOR EACH ROW
BEGIN ATOMIC
    IF (NEW.DATA_DOCUMENTO < (SELECT HORARIO_SAIDA FROM DIARIAS WHERE ID = NEW.FK_DIARIA_ID) OR 
         NEW.DATA_DOCUMENTO > (SELECT HORARIO_CHEGADA FROM DIARIAS WHERE ID = NEW.FK_DIARIA_ID)) THEN
         -- Dispara a exceção SQL 22007, e cancela a operação.
         -- SQL22007 - Um formato de data/hora inválido foi detectado, ou seja, um valor ou representação de cadeia inválido foi especificado
         SIGNAL SQLSTATE '22007'; 
    END IF;
END

Pelo que vi, na atribuição de uma variável SQL no HSQLDB em triggers, stored procedures ou functions, não pode-se utilizar um select com parâmetros dinâmicos, somente estáticos, imagino que era por isso que dava aquele erro.
Dessa forma funcionou perfeitamente.

Obrigado drsmachado e ademilton, pela solicitude.
Thx :smiley:

#7