Triggers em SQL Server, Oracle, Firebird e Postgres

Ao trabalharmos com bancos de dados, temos a ideia de que eles trabalham praticamente da mesma forma, o que n�o � verdade, pois cada um possui as suas peculiaridades. Neste artigo apresentaremos um comparativo entre os principais bancos de dados em relaç�o a alguns itens que s�o importantes no momento em que trabalhamos com aplicaç�es diversas, como � o caso de utilizaç�o de triggers, coment�rios e condicionais comparaç�o ocorrer� entre os bancos de dados SQL Server, Oracle e Firebird.

Utilizaç�o de triggers

No que diz respeito a triggers, o Oracle e o SQL Server possuem conceitos diferentes para a sua criaç�o.

O SQL Server possui uma abordagem baseada em conjuntos, tendo que as linhas que s�o afetadas por uma modificaç�o de dados, sejam insert, update ou delete, s�o armazenadas nas tabelas tempor�rias inserted e deleted. Em caso de triggers regulares do tipo DML (Data Model Language), DDL (Data Definition Language) e de Logon, estas ser�o sempre executadas ap�s a passagem de uma instruç�o. Neste momento, tem-se que uma �imagem� do registro � criada antes da informaç�o definitiva ser armazenada na tabela e, em seguida, a �imagem� � eliminada ap�s o registro ser inserido definitivamente na tabela. Ambos podem ser acessados de dentro da trigger. No entanto, quando � necess�rio, pode-se juntar as tabelas tempor�rias inserted e deleted para realizar a atualizaç�o da tabela quando a trigger for executada.

No Oracle s�o encontrados os termos AFTER e BEFORE no corpo das triggers, que podem ser executadas tanto por linha quanto por declaraç�o. Com isso, as triggers utilizando BEFORE s�o disparadas antes da ocorr�ncia de uma aç�o. J� as triggers apresentadas com o AFTER ocorrem ap�s a aç�o ter sido executada.

Nos tr�s bancos temos momentos como no Oracle. Por�m, no SQL Server temos os momentos AFTER e INSTEAD OF, mas n�o o momento BEFORE, enquanto que o Firebird suporta m�ltiplos disparos da trigger a n�vel de linha por tabela, o que n�o ocorre com o Oracle.

No que diz respeito ao Oracle, quando trabalhamos com triggers BEFORE, estas n�o podem ver uma imagem que seja declarada como AFTER, onde de igual forma uma trigger declarada como AFTER n�o poder� ver uma imagem que seja declarada com BEFORE. Apenas triggers de linha podem visualizar as imagens BEFORE e AFTER, ao mesmo tempo. Nestes casos em que utilizamos as triggers de linha, podemos fazer refer�ncia aos valores contidos nas colunas, sabendo qual a informaç�o que existia antes de uma atualizaç�o e ap�s essa atualizaç�o ter sido realizada. Para isso, a Oracle oferece os pseudorecords OLD e NEW, os quais s�o utilizados para acessar as informaç�es antigas e novas, respectivamente. Com base nisso, podemos dizer que o OLD e o NEW s�o, portanto, semelhantes as tabelas tempor�rias deleted e inserted, presentes no SQL Server, com a diferença que estes est�o em n�vel de linha. Outra propriedade presente nas triggers da Oracle, que tamb�m � uma fonte regular de erros, � quando a tabela na qual a trigger est� atuando � chamada por uma outra operaç�o qualquer como, por exemplo, uma consulta.

Uma outra diferença entre o Oracle e o Firebird � que o Firebird n�o levanta exceç�es referentes a mutaç�o de tabelas, como a Oracle faz, e as triggers, por padr�o, podem ser aninhadas e recursivas, diferentemente do SQL Server, que n�o aceita recursividade por padr�o. No SQL Server as operaç�es de trigger recursiva s�o poss�veis, mas pelo menos para triggers normais este n�o � o comportamento padr�o, al�m de que triggers disparando de forma recursiva precisam ser definidas explicitamente como uma opç�o de servidor.

As triggers do Firebird usam as vari�veis de contexto NEW e OLD e prov�m flag de inserç�o, atualizaç�o e exclus�o para indicar o status atual da trigger.

J� o PostgreSQL � muito parecido com o Firebird, pois para obtermos as informaç�es basta atribuirmos o prefixo OLD para realizarmos as consultas, e o NEW quando for um novo valor. Eles, por conseguinte, s�o diferentes do Oracle devido a utilizaç�o do (:) antes dos termos OLD e NEW. Com relaç�o a compilaç�o das triggers, temos que o banco de dados exclui normalmente as vers�es anteriores, caso j� tenham sido compiladas anteriormente. Dessa forma, tanto para o Postgres quanto para o Oracle, basta utilizarmos a instruç�o CREATE ou REPLACE, enquanto que no SQL Server precisamos verificar no dicion�rio de dados se o objeto com mesmo nome j� existe.

Antes de começarmos com os exemplos, torna-se necess�rio a visualizaç�o da sintaxe apresentada por cada uma das bases de dados para a criaç�o de uma trigger, o que podemos ver de acordo com as Listagens 1, 2, 3 e 4, respectivamente Oracle, SQL, Firebird e PostgreSQL. Vale salientar que as triggers que ser�o vistas ser�o referentes a operaç�es DML.


    CREATE [OR REPLACE ] TRIGGER trigger_name 
      {BEFORE | AFTER | INSTEAD OF } 
      {INSERT [OR] | UPDATE [OR] | DELETE} 
      [OF col_name] 
      ON table_name 
      [REFERENCING OLD AS o NEW AS n] 
      [FOR EACH ROW] 
      WHEN (condition)  
      DECLARE
         Declaration-statements
      BEGIN 
         Executable-statements
      EXCEPTION
         Exception-handling-statements
      END;

Listagem 1. Sintaxe b�sica de uma trigger no Oracle


    -- Sintaxe do SQL Server 
      Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
      CREATE TRIGGER [ schema_name . ]trigger_name 
      ON { table | view } 
      [ WITH <dml_trigger_option> [ ,...n ] ]
      { FOR | AFTER | INSTEAD OF } 
      { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
      [ WITH APPEND ]
      [ NOT FOR REPLICATION ] 
      AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }
       
      <dml_trigger_option> ::=
          [ ENCRYPTION ]
          [ EXECUTE AS Clause ]
      <method_specifier> ::= 
          assembly_name.class_name.method_name

Listagem 2. Sintaxe b�sica da trigger no SQL Server


      {CREATE | RECREATE | CREATE OR ALTER} TRIGGER name FOR {TABLE name | VIEW name}
       [ACTIVE | INACTIVE]
       {BEFORE | AFTER}
       {INSERT [OR UPDATE] [OR DELETE] | UPDATE [OR INSERT] [OR DELETE] | DELETE [OR UPDATE] [OR INSERT] }
       [POSITION n] AS
      BEGIN
       .....
      END

Listagem 3. Sintaxe b�sica de uma trigger no Firebird


    CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
        ON table_name
        [ FROM referenced_table_name ]
        [ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } ]
        [ FOR [ EACH ] { ROW | STATEMENT } ]
        [ WHEN ( condition ) ]
        EXECUTE PROCEDURE function_name ( arguments )

Listagem 4. Criaç�o de uma trigger no Postgres

Vejamos um exemplo simples de como podemos criar triggers para cada uma das bases de dados apresentadas neste artigo. Começaremos vendo a criaç�o de uma trigger no Oracle, onde apresentaremos uma simples mensagem contendo os valores de sal�rios novos, antigos e a diferença entre eles, como apresentado pela Listagem 5.


    CREATE OR REPLACE TRIGGER Muda_SalarioFuncionario
        BEFORE DELETE OR INSERT OR UPDATE ON Funcionarios
        FOR EACH ROW
      WHEN (new.IdFuncionario > 0)
      DECLARE
          diferenca number;
      BEGIN
          diferenca := :new.salarioFuncionario - :old.salarioFuncionario;
          dbms_output.put('o sal�rio antigo do funcion�rio foi de ' || :old.salarioFuncionario);
          dbms_output.put(' o sal�rio atual do funcion�rio � de ' || :new.salarioFuncionario);
          dbms_output.put_line('A diferença entre os sal�rios do funcion�rio � de ' || diferenca);
      END; 
      /

Listagem 5. Exemplo simples de criaç�o de uma trigger no Oracle

A trigger � disparada quando uma das operaç�es DML (INSERT, UPDATE e DELETE) � realizada sobre a tabela. Devido ao fato de estarmos utilizando a palavra-chave BEFORE, a trigger pode acessar os novos valores antes que estes sejam inseridos na tabela, al�m da possibilidade de poder alterar o valor utilizando a atribuiç�o :NEW.Column_name.

J� com relaç�o ao AFTER, este tamb�m pode ser utilizado, mas apenas para consultar ou alterar as informaç�es presentes na tabela, ap�s as mudanças iniciais terem sido realizadas e a tabela estiver com os dados j� gravados e consistentes. Estamos utilizando neste caso a cl�usula FOR EACH ROW, para que possamos executar a instruç�o v�rias vezes, o que ocorre em casos de atualizaç�o ou exclus�o de v�rias linhas ao mesmo tempo.

Ap�s criarmos a trigger, realizaremos agora uma inserç�o na tabela de Funcion�rios e em seguida, uma atualizaç�o no item salarioFuncionario para que possamos ver as informaç�es serem apresentadas. Para que possamos inserir os dados de teste na tabela, temos que a query de inserç�o e de update que ser�o utilizadas est�o sendo apresentadas pela Listagem 6.


      -- Instruç�o de Insert para gravar os registros dos funcion�rios.
      INSERT INTO Funcionarios (IdFuncionario, NomeFunc, EnderecoFunc, salarioFuncionario) 
      VALUES (1, �Edson Dionisio�, �Rua dos testes�, 3000);
      INSERT INTO Funcionarios (IdFuncionario, NomeFunc, EnderecoFunc, salarioFuncionario) 
      VALUES (5, �Jos� Edson dos Santos�, �Starling city�, 2000);
      INSERT INTO Funcionarios (IdFuncionario, NomeFunc, EnderecoFunc, salarioFuncionario) 
      VALUES (9, �Tatsu Yamashiro�, �Rua dos Navegantes�, 4500);
      INSERT INTO Funcionarios (IdFuncionario, NomeFunc, EnderecoFunc, salarioFuncionario) 
      VALUES (15, �Carlos Edson Andrade�, �Rua dos testes�, 1700);
      INSERT INTO Funcionarios (IdFuncionario, NomeFunc, EnderecoFunc, salarioFuncionario) 
      VALUES (25, �Mariana Ximenes�, �Rio de Janeiro�, 12000);
      -- Instruç�o de update para atualizar os registros dos funcion�rios.
      UPDATE Funcionarios SET salarioFuncionario = salarioFuncionario + 1500 
      WHERE NomeFunc LIKE �%Edson%�;

Listagem 6. Inserç�o e atualizaç�o de dados na base de dados

Ao utilizarmos a instruç�o UPDATE, ela ir� disparar a trigger para cada linha que dever� ser atualizada, apresentando dessa forma tanto o sal�rio novo do funcion�rio, quanto o antigo e ainda a diferença entre os valores.

O pr�ximo exemplo que veremos ser� referente a trigger do banco de dados Firebird, onde utilizaremos o mesmo conceito de mudança de sal�rio, como mostra a Listagem 7.


    create trigger audita_salarioFuncionario for funcionario
         after update
      as
      begin
         if (old.salarioFuncionario <> new.salarioFuncionario) then
            insert into historico
               (idfuncionario, datamodificacao,
                usuario, salarioFuncionario_antigo,
                percentagem
               )
               values (
                  old.idfuncionario, 'now', user, old.salarioFuncionario,
                  (new.salarioFuncionario/old.salarioFuncionario-1) * 100
               );
      end;

Listagem 7. Exemplo de trigger no Firebird

A trigger realiza uma atualizaç�o das informaç�es principais referentes a mudança do sal�rio de um funcion�rio, apresentando al�m disso a porcentagem de mudança entre o sal�rio antigo e o novo sal�rio. Mas para isso, verificamos antes se o novo sal�rio � diferente do antigo para que possamos realizar as operaç�es necess�rias.

Por �ltimo, temos o exemplo que mostra como criar a trigger no SQL Server, que tem o objetivo de que, quando um registro for inserido na tabela funcion�rios, tamb�m realizemos a inserç�o dos dados na tabela de backup, mantendo uma c�pia de algumas das informaç�es do funcion�rio na tabela funcionarioBackup. Ap�s a criaç�o das tabelas, iremos preench�-las com alguns dados, como mostra a Listagem 8.


    create trigger trg_copiafuncionario 
      on dbo.funcionario
      after insert as
      begin
      insert into funcionarioBackup
      select * from inserted
      end
      go

Listagem 8. Exemplo de trigger no SQL Server

Ao criarmos nossa trigger, inserimos um novo registro na tabela de funcion�rios, onde neste mesmo instante inserimos um registro na tabela de funcionarioBackup, pois no momento da criaç�o da trigger utilizamos o AFTER INSERT na tabela de funcionarios, o que nos diz que, ap�s a inserç�o do registro na tabela de funcion�rios, ser� realizada a inserç�o dos mesmos dados na tabela de c�pia.

A tabela INSERTED � uma tabela tempor�ria, que tem por objetivo manter apenas as linhas que s�o inseridas e, devido a isso, escolhemos esta opç�o para manter as informaç�es sincronizadas em ambas as tabelas. Para testarmos a nossa trigger, iremos inserir alguns registros na tabela de funcion�rios e, em seguida, realizaremos um select na tabela de funcionarioBackup para ver o resultado, como mostra a Listagem 9.


    insert into funcionario (codigoFunc, nomefunc, email, telefone) 
    values (5, 'Edson Dionisio', 'edson.dionisio@gmail.com', '81997402801') 
    insert into funcionario (codigoFunc, nomefunc, email, telefone) 
    values (8, 'Mait� Silva', 'maite.silva@gmail.com', '81997402801') 
    insert into funcionario (codigoFunc, nomefunc, email, telefone) 
    values (12, 'Marilia K�ssia', testekessia@gmail.com', '81997402801')
    select * from funcionario
    select * from funcionariobackup 
    go

Listagem 9. Inserç�o de dados na tabela de funcion�rios

Ap�s a inserç�o ter sido realizada na tabela de funcion�rios, podemos ver que as mesmas informaç�es foram salvas na tabela de funcionarioBackup, como era de se esperar.

Podemos ver com isso que a utilizaç�o das triggers facilita bastante os processos repetitivos, auditoria de informaç�es, relat�rio de erros, dentre outras opç�es que podemos achar necess�rio para a criaç�o de uma trigger.

Um novo exemplo simples a ser adicionado ser� com relaç�o a base de dados Postgres: nele iremos inserir dados numa tabela chamada Item e verificaremos a quantidade de itens existentes. Caso esta quantidade atinja o seu limite, teremos uma mensagem de erro disparada pela Trigger. Para vermos este exemplo podemos primeiramente criar uma tabela no Postgres, chamada Item, e a ela adicionaremos apenas os campos idItem e produto. Em seguida, teremos a nossa trigger definida de acordo com a Listagem 10.


    -- Criando a funç�o 
    CREATE OR REPLACE FUNCTION item()
      RETURNS trigger AS
    $BODY$
    Declare LTOTALITENS INT;
    Declare LNOTA INT;
    BEGIN
    
    -- Quantidade de itens inseridos na nota
    Select COUNT(*) into LTOTALiTENS From "Item";
    
    If (LTOTALITENS > 6) Then
    Raise Exception 'A nota % j� est� cheia. Inclus�o cancelada.', LNOTA;
    End If;
    Return New;
    END
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;
    ALTER FUNCTION item()
      OWNER TO postgres;
    
    -- Criando a trigger
    create trigger "InsereItem" 
    BEFORE INSERT ON "Item" FOR EACH ROW
    EXECUTE PROCEDURE ITEM();

Listagem 10. Criando a trigger pelo Postgres

Trabalhando com Coment�rios

No que diz respeito a coment�rios, n�o h� muita diferença entre as bases de dados SQL Server, Oracle e Firebird, pois ambas permitem tanto coment�rio de linha quanto coment�rios de m�ltiplas linhas.

Os coment�rios de uma linha s�o representados pelos s�mbolos (--) ou (/**/) para coment�rios de m�ltiplas linhas. Um exemplo que representa ambos os bancos de dados pode ser visto na Listagem 9.


    /* Criaç�o de uma tabela Clientes
      * que tem apenas o objetivo de apresentar os coment�rios de m�ltiplas linhas.
      */
      create table Clientes (
        nomeFunc varchar(100),            -- NomeFunc do cliente
        enderecoFunc varchar(255), /* este pode conter o endereço do cliente. 
        Para este s�o adicionadas as seguintes informaç�es: 
      * Rua,
      * cep,
      * Estado, etc
      */
        telefone varchar(14),  -- Campo para atribuiç�o do telefone
        email varchar (200)       -- Campo respons�vel pela email do usu�rio
      )

Listagem 11. Coment�rios de �nica linha e de m�ltiplas linhas

O que ocorre no Oracle � que podemos adicionar instruç�es SQL como parte do c�digo da aplicaç�o que executa as instruç�es, al�m de adicionar coment�rios associados a esquema ou objetos nonschema individuais, que s�o armazenados no dicion�rio de dados junto aos metadados dos pr�prios objetos.

Para coment�rios referentes a Schema e objetos NonSchema, podemos usar o comando COMMENT para associar um coment�rio com um objeto de esquema (tabela, view, operadores, indextype, etc). Al�m disso, temos a possibilidade de criar um coment�rio sobre uma coluna espec�fica, que � parte de um objeto de esquema da tabela. Os coment�rios associados a esquemas e objetos nonschema s�o armazenados no dicion�rio de dados.

Trabalhando com IF

De igual forma �s linguagens de programaç�o, as estruturas condicionais IF...ELSE s�o utilizadas para que sejam estabelecidas condiç�es no decorrer da execuç�o do fluxo de c�digo, onde temos a atribuiç�o de duas ou mais possibilidades de resultado que podem ser executados.

No Oracle, a instruç�o IF-THEN-ELSE pode variar, onde dependendo do caso, podemos usar a sintaxe IF-THEN-ELSIF, quando precisamos executar um conjunto de instruç�es quando condition1 � TRUE ou um conjunto diferente de declaraç�es passa a ser TRUE. Um exemplo dessa utilizaç�o pode ser visto na Listagem 10.


    IF salarioFuncionario >= 800 AND salarioFuncionario <= 1400 THEN
      profissao := 'Estagi�rio em Desenvolvimento de sistemas';
    ELSIF salarioFuncionario > 1400 and salarioFuncionario <= 4000 THEN
      profissao := 'Desenvolvedor Junior';
    ELSIF salarioFuncionario > 4000 and salarioFuncionario <= 9000 THEN
      profissao := ' Desenvolvedor Junior';
    ELSE
      profissao := 'N�o h� uma profiss�o cadastrada';
    END IF;

Listagem 12. Utilizaç�o do IF-THEN-ELSIF da Oracle

Com o Firebird n�o ocorre essa variaç�o, pois ele apresenta apenas a estruturaç�o IF-THEN-ELSE. No entanto, ele possui uma particularidade que � uma forma diferente de escrever a instruç�o, a qual � apresentada por IIF(), que pode ser comparada a uma operaç�o tern�rio do C#. A sintaxe para esse caso � apresentada na Listagem 11.


    IIF (<condiç�o>, ResultadoA, ResultadoB)
      <condiç�o>  ::=  Express�o booleana.

Listagem 13. Apresentaç�o da sintaxe IIF()

Para que possamos entender melhor esta sintaxe, apresentaremos tanto a forma normal da estrutura de fluxo apresentada pelo Firebird quanto a sintaxe apresentada pela Listagem 11. Vejamos ent�o as Listagens 12 e 13.


    if (salarioFuncionario < 3000) then
        funcionario = 'Pode ser estagi�rio!';
      else
        funcionario = '� desenvolvedor pleno';

Listagem 14. Sintaxe IF...THEN...ELSE


    select iif( sexo = 'Masculino', 'Sr', 'Sra' ) from Clientes

Listagem 15. Sintaxe IIF()

Por �ltimo, temos o SQL Server, que possui a estrutura representada apenas por IF-ELSE, e apresentando de igual forma a estrutura particular IIF(). Podemos ver um exemplo na Listagem 16.


    -- Utilizando a base de dados exemplo do SQL Server.
      USE AdventureWorks2012;
      GO
      /* Realizando a contagem de produtos que tenham o nomeFunc iniciado por touring-3000 
      E em caso de existir, apresentar uma mensagem.
      */
      if (select count(*) from production.product where name like 'touring-3000%' ) > 5
      print 'Existem mais que 5 bicicletas touring-3000 bicycles cadastradas.'
      else 
      print 'Existem 5 ou menos bicicletas touring-3000 na base de dados.' ;
      go

Listagem 17. Utilizando IF...ELSE no SQL Server

Veja que a diferença na sintaxe n�o � t�o grande em relaç�o a forma de processamento, principalmente no que tange o uso de triggers.

Links:

Desenvolvendo Triggers em SQL Server, Oracle, Firebird e Postgres

Confira nesse artigo um comparativo entre os três principais bancos de dados: Oracle, SQL Server, Firebird e PostgreSQL. Veremos como usar triggers, comentários e a condicional IF.

Utilizaç�o de triggers

Trabalhando com Coment�rios

Trabalhando com IF

Artigos relacionados