Explorando Triggers no Firebird

O Firebird é um sistema gerenciador de Banco de Dados que roda em diversas plataformas como Linux, Windows, Mac OS, e em uma variedade de plataformas Unix.

A fundação FirebirdSQL é a responsável pela coordenação, manutenção e desenvolvimento do Firebird. O código do Firebird está disponível no SourceForge.

O banco de dados Firebird é baseado no código do InterBase da Borland que teve o seu código-fonte aberto na versão 6.0 em 25 de julho do ano de 2000. Desde então alguns programadores assumiram o projeto identificando e corrigindo os inúmeros defeitos da versão original, surgindo então o Firebird 1.0, que passou a contar com características próprias, obtendo uma aceitação imediata e bastante grande de diversos programadores e organizações ao redor do planeta.

Atualmente o Firebird vem ganhando novas versões frequentemente e continua sendo totalmente gratuito sem qualquer tipo de limitação e possui um grande suporte em listas na internet.

O Firebird é conhecido por ser um banco de dados seguro e confiável. A tecnologia utilizada no Firebird tem mais de 20 anos, fazendo com que ele seja também um produto muito maduro e estável. Com o Firebird podemos gerenciar bancos de dados de alguns Kbytes até dezenas de Gigabytes com uma boa performance e praticamente sem qualquer necessidade de manutenção. Um exemplo dessa afirmação está documentado no artigo do Ib-aid que descreve o uso do Firebird com um banco de dados de 1 Terabyte. Nas referências bibliográficas consta esta referência.

Entre os recursos oferecidos pelo Firebird temos: compatibilidade com transações ACID, transações MVCC, Triggers, Procedures, Collations, Multi Generational Architecture, pouco consumo de recursos de processamento, linguagem nativa para Triggers e Stored Procedures, Suporte para Funções Externas (UDFs), quase nenhuma configuração para instalação e uso, centenas de ferramentas de terceiros que inclui aplicações gráficas para tarefas administrativas, Careful writes para rápida recuperação, diversas formas de acesso ao banco de dados, backups incrementais, total controle de cursores em PSQL, Tabelas de Monitoramento, Tabelas temporárias, TraceAPI para saber o que está ocorrendo no servidor, entre diversos outros recursos. Mais adiante neste artigo veremos uma das funcionalidades do Firebird que são as Triggers.

O Firebird é disponibilizado em quatro versões: SuperServer, Classic, SuperClassic e Embedded. A versão SuperServer compartilha um cache entre as conexões com o banco, e utiliza threads para gerenciar cada conexão. A versão Classic é indicada para máquinas com mais de um processador e para algumas outras situações específicas. Na Classic também temos um processo independente do servidor para cada conexão estabelecida. O SuperClassic usa threads em um único processo do servidor, com cache independente para cada conexão. Por fim, a versão Embedded é uma variação do servidor em que consiste em um servidor Firebird completo composto por apenas alguns arquivos, facilitando assim a sua distribuição, pois não há necessidade de instalação.

O Firebird vem com uma série de utilitários de linha de comando que permitem criar bancos de dados, recuperar estatísticas, executar comandos e scripts SQL, efetuar backups e restores, etc. Para isso podemos utilizar a ferramenta Firebird ISQTL Tool que está incluída na instalação. Caso seja preferível uma ferramenta com interface gráfica, existem diversas opções incluindo gratuitas. Entre as opções mais conhecidas com interface gráfica temos o IBExpert, FlameRobin, DB Workbench, EMS SQL Management Studio e o Firebird Development Studio. Para o nosso exemplo utilizaremos o FlameRobin que é open source e executa em diversas plataformas. Além disso, o Firebird possui uma vasta quantidade de Drivers e componentes de acesso como o IB Objects, FIBPlus, UIB e o Zeos para Delphi, Firebird ODBC Driver e o EasySoft para ODBC, IBProvider para OLEDB, JayBird para Java, Firebird .Net Provider para plataforma .Net, IBPP para C++, entre outras.

Veremos no restante do artigo o que são Triggers, quando elas devem ser utilizadas e quando não devem ser utilizadas e por fim, faremos um exemplo prático de como podemos criar uma Trigger para realizar a autoria em uma tabela no banco de dados.

Triggers

Uma Trigger é um código que executa apenas antes ou depois que um evento de INSERT, UPDATE ou DELETE ocorrem em uma tabela da base de dados. Este código que roda na Trigger pode verificar ou modificar dados de entrada, efetuar cálculos, executar comandos SQL, etc.

As Triggers são suportadas em diversas bases de dados como Firebird, Oracle, DB2, Microsoft SQL Server, MySQL, PostgreSQL, SQLite, e muitos outros. A implementação de uma Trigger varia de um banco de dados para outro, por isso é importante sempre verificarmos a documentação associada.

Outro tipo de Trigger suportada é a "Trigger Temporal" que consiste de uma Trigger agendada que é disparada dependendo do tempo, ao invés de ser disparada quando uma atualização ocorre em uma tabela. Esse tipo de Trigger é utilizada para limpar logs, calcular informações, arquivar informações, etc. Porém esse tipo de Trigger não é suportada em todas as bases de dados.

As Triggers podem ser simples ou complexas e resolver diferentes tipos de problemas, porém existem situações que devemos evita-las, como as apontadas abaixo:

• Triggers não devem ser utilizadas no lugar de chaves estrangeiras. Chaves estrangeiras impõem a integridade referencial, impossibilitando que a inserção, deleção ou edição de dados deixem “registros órfãos”.
• Triggers não são substitutas para as transações. O código da Trigger pode falhar, assim devemos envolver as atualizações em uma transação única.
• Devemos ser cautelosos ao dividir a lógica de negócio entre o banco de dados e os sistemas de back-end escrito em PHP, C#, Java, entre outros. A manutenção ficará cada vez mais complicada se o código está separado.
• Evitar a duplicação de esforços é algo que deve ser considerado. O código no back-end deve validar entradas do usuário, não sendo necessário repeti-los no banco de dados.
• Triggers causam sobrecarga no desempenho. Mesmo que as Triggers executem mais rápido do que uma série de comandos SQL no código back-end, devemos evitar Triggers muito complexas que realizam com regularidade muitas modificações nas tabelas.

No entanto, devemos usar Triggers quando queremos automatizar alterações que são específicas para a base de dados ou para o gerenciamento dessas informações. Um bom exemplo disso é um log de auditoria. Triggers também podem ser utilizadas para reduzir a complexidade e aumentar a performance.

Basicamente uma Trigger no Firebird tem a sintaxe apresentada pela Listagem 1.

        CREATE TRIGGER name FOR {table view}
        [ACTIVE | INACTIVE]
        {BEFORE AFTER} {DELETE INSERT UPDATE}
        [POSITION number]
        AS
        <variable_declaration_list> =DECLARE VARIABLE variable datatype;
        [DECLARE VARIABLE variable datatype;...]
        BEGIN
        /* Código da Trigger aqui */
        END
        

Podemos verificar alguns argumentos acima como "table view" que é o nome tabela ou view que estará associada à trigger sendo criada, "active ou inactive" indica se a trigger estará ativa ou não, "before ou after" indica em que momento a trigger será disparada, ou seja, antes ou depois do eventos de UPDATE, INSERT ou DELETE. Por fim, "position" é utilizado para definir a sequência de execução das triggers que estão associadas a um mesmo momento e a um mesmo evento. Por exemplo, podemos imaginar que temos duas triggers do tipo BEFORE DELETE, porém como podemos saber qual delas será executada primeiro? Este parâmetro "position" indicará isso.

Segue na Listagem 2 o exemplo de uma Trigger vazia que pode ser compilada no Firebird.

        CREATE TRIGGER CLIENTE_TESTE FOR CLIENTE
        ACTIVE BEFORE INSERT POSITION 0
        AS
        BEGIN
        /*Código da Trigger aqui*/
        END
        

Também podemos alterar uma Trigger, conforme a sintaxe da Listagem 3.

        {ALTER TRIGGER name} {CREATE OR ALTER TRIGGER name FOR {table view}
        [ACTIVE | INACTIVE]
        [{BEFORE | AFTER} {DELETE | INSERT | UPDATE}]
        [POSITION number]
        AS <trigger_body>;
        

Também podemos excluir uma Trigger, conforme o comando da Listagem 4.

        DROP TRIGGER nome_trigger;
        

Posteriormente à criação da Trigger também podemos desativá-la, conforme a Listagem 5 mostra.

        ALTER TRIGGER nome_trigger INACTIVE;
        

Na próxima seção veremos como podemos criar Triggers diretamente no Firebird e faremos um exemplo prático de auditoria de uma tabela.

Exemplo Prático Utilizando Triggers

Para exemplificar o uso de Triggers implementaremos um controle de auditoria bastante eficiente e simples que é utilizado em diversas aplicações. Ao invés de realizarmos a auditoria pelo código da aplicação, faremos pelo banco de dados usando triggers. Manter esse tipo de lógica no código não é interessante, pois os clientes ficam mais complexos, o sistema fica sujeito a erros podendo inviabilizar o uso do sistema como um todo, entre outros.

Neste exemplo faremos uma auditoria nos salários dos funcionários, controlando todas as atualizações que forem realizadas no sistema. Poderíamos acrescentar outros atributos também como o IP de quem fez a alteração, a data da alteração, etc. Porém, para fins de exemplificação manteremos o registro de apenas alguns atributos.

Primeiramente devemos baixar o Firebird através do seu site principal. Para o nosso exemplo usaremos a versão 2.5.3. Para criar as tabelas e a trigger podemos utilizar o Firebird ISQL Tool que vem junto com a instalação do Firebird ou podemos baixar o FlameRobin que é uma ferramenta visual para manipular o Banco de dados do Firebird. Para baixar o FlameRobin podemos fazer o download no seu site principalou podemos baixar diretamente aqui.

Para exemplificação utilizaremos tanto o Firebird ISQL Tool quanto o FlameRobin.

Inicialmente abra o Firebird ISQL Tool e crie a base de dados conforme mostra a Figura 1.

Após digitarmos o comando para criar a base de dados e dar um Enter devemos colocar as credenciais em “CON>”. Segue abaixo a Figura 2 que mostra as credenciais para criarmos a base de dados.

Não devemos esquecer-nos de adicionar um ponto e vírgula no final da linha. Conforme mostra a imagem o banco foi corretamente criado.

Se visitarmos o diretório especificado na criação da base de dados podemos verificar que ele se encontra agora criado conforme mostra a Figura 3 abaixo.

O próximo passo é criar as tabelas para o nosso exemplo de auditoria. Primeiramente vamos criar a tabela “SALARIO” que conterá os salários dos funcionários atualizados. Assim sendo, crie a tabela conforme o comando da Listagem 6.

        CREATE TABLE SALARIO
        (
        ID Integer NOT NULL,
        SALARIO Decimal(18,2),
        LOGIN Varchar(200),
        PRIMARY KEY (ID)
        );
        

Após isso devemos criar a tabela de auditoria que conterá todas as atualizações de salário que forem realizadas na tabela acima. Segue na Listagem 7 o comando que cria a tabela “AUDIT_SALARIO”.

        CREATE TABLE AUDIT_SALARIO
        (
        SALARIO_ANTERIOR Decimal(18,2),
        SALARIO_NOVO Decimal(18,2),
        LOGIN Varchar(200)
        );
        

As Figuras 4 e 5 demonstram a criação das tabelas e a inserção de alguns registros para verificar se está tudo funcionando perfeitamente no Banco de dados do Firebird.

Também podemos utilizar o FlameRobin para manipular a base de dados do Firebird. Na Figura 6 segue o FlameRobin sendo executado e a sua tela principal.

Para manipular a base de dados do Firebird devemos registrar a base de dados dentro do FlameRobin. Para isso clique com o botão direito do mouse na opção "Localhost" e selecione a opção "Register existing database". Segue a opção na Figura 7 abaixo.

Agora basta selecionar a base de dados criada anteriormente conforme ilustra a Figura 8 abaixo.

Após selecionar a base de dados do sistema de arquivos o FlameRobin seleciona automaticamente o “Display name” e devemos agora apenas colocar o “User name” e o “password” que foram também criados anteriormente. Após isso basta clicar em “Save”. A Figura 9 abaixo mostra este processo descrito.

Podemos verificar que agora a base de dados é mostrada na janela principal do FlameRobin conforme mostra a Figura 10 abaixo.

Agora podemos conectar na base de dados para que possamos manipular o banco de dados por meio do FlameRobin. Para isso basta clicar com o botão direito do mouse em cima do nome da base de dados e clicar em "connect" conforme mostra a Figura 11.

Agora diversas opções estão disponíveis para que possamos manipular a base de dados por meio do FlameRobin de forma visual, conforme mostra a Figura 12.

Após isso vamos criar uma Trigger, onde para cada alteração de salario será criado uma linha de log na tabela AUDIT_SALARIO. Para isso podemos utilizar o FrameRobin. Dessa forma, basta clicar em “New Trigger”. Quando abrir a janela para digitação da Trigger basta digitar o código da Listagem 8.

        SET TERM ^ ;
        CREATE TRIGGER AUDITASALARIOFUNC FOR SALARIO
        ACTIVE
        BEFORE UPDATE
        POSITION 0
        AS
        BEGIN
        insert into AUDIT_SALARIO (LOGIN, SALARIO_ANTERIOR, SALARIO_NOVO) VALUES (OLD.LOGIN, OLD.SALARIO, NEW.SALARIO);
        END^
        SET TERM ; ^
        

Segue na Figura 13 a tela do FlameRobin com a criação da Trigger.

Podemos verificar que conseguimos acesso ao valor anterior e ao valor posterior no momento em que a trigger foi disparada.

Para termos acesso a estas informações no Firebird, basta acrescentarmos o prefixo OLD para consultar o valor anterior e NEW para o valor sendo inserido. Essa é uma facilitando bastante interessante que o Firebird oferece.

Outra forma de fazer isso seria utilização a atribuição de variáveis no SELECT. Quando selecionamos o conteúdo de uma ou mais tabelas podemos usar o resultado da consulta, para isso carregamos o resultado em variáveis locais da Trigger. Segue um exemplo na Listagem 9.

        Select SALARIO
        From SALARIO
        Where LOGIN = 'felipelima'
        Into :SALARIOANTERIOR;
        

Outra dúvida que pode vir a surgir é em relação ao "SET TERM" que está no código da Trigger acima. O FlameRobin identifica o fim de cada comando pelo caractere “;”. Como estamos criando uma Trigger queremos que o FlameRobin execute o comando "CREATE TRIGGER" como um único comando, o que significa que ele deveria terminar com um “;”. Porém, como cada comando no corpo da trigger que está sendo criada também termina com “;” o FlameRobin acharia que encontrou o fim do CREATE TRIGGER quando ele encontrasse o primeiro “;”. Dessa forma, devemos contornar isso utilizamos o comando SET TERM. Esse tipo de situação também ocorre em outras ferramentas como a IBOConsole.

Por fim, devemos clicar em “Execute Statement” e “Commit Transaction” conforme as imagens abaixo:

Agora verificamos que a trigger foi criada na Base de Dados, conforme mostra a Figura 15.

Agora para exemplificar vamos atualizar o salário do usuário criado anteriormente “felipelima”. Dessa forma, abra novamente a janela do Firebird ISQL Tool e insira o comando da Listagem 10 (este procedimento também pode ser feito no FlameRobin).

        UPDATE SALARIO SET SALARIO = 3.500 WHERE LOGIN = 'felipelima';
        

Segue na Figura 16 a tela do Firebird ISQL Tools mostrando o comando sendo executado.

Feito isso a nossa Trigger deve ter sido disparada, gerando assim uma nova linha na tabela AUDIT_SALARIO comportando essa alteração. Segue na Figura 17 o retorno de um SELECT na tabela AUDIT_SALARIO.

Portanto, para cada registro atualizado teremos um registro incluído na nossa tabela de auditoria.

Como uma última dica para o exemplo acima poderíamos também criar uma Trigger para um mecanismo de auto incremento no Firebird. Para isso precisaríamos de uma trigger e um generator (contador).

Dessa forma, primeiramente criamos um generator conforme a Listagem 11.

        CREATE GENERATOR GEN_PK_SALARIO;
        

E depois a trigger para sempre incrementar o valor em ID, como mostra a Listagem 12.

        SET TERM !;
        CREATE TRIGGER GERA_ID_SALARIO
        FOR SALARIO
        ACTIVE
        BEFORE INSERT
        POSITION 0
        AS
        BEGIN
        IF (NEW.ID IS NULL) THEN BEGIN
        NEW.ID = GEN_ID(GEN_PK_SALARIO, 1);
        END
        END;

Bibliografia

• Firebird Manual
• FlameRobin
• Why to create terabyte Firebird database?