Desvendando o SQL - Parte XI - Gatilhos

De volta com nosso último artigo da série sobre a linguagem SQL, caso não tenha acompanhado os artigos anteriores, no final do artigo coloco o link para os anteriores. Hoje vamos mostrar como funcionam as TRIGGERS ou como alguns conhecem os GATILHOS do SQL. Boa leitura a todos.

DEFINIÇÃO


Os gatilhos são um tipo especial de procedimento armazenado que é executado automaticamente na ocorrência de eventos no banco de dados.

Existem os gatilhos DML que ocorrem no momento da modificação dos dados de uma tabela, ou seja, quando estamos manipulando os dados, quando executados INSERTS, UPDATE E DELETES, e são disparados quando um evento válido é acionado.

Existem também os gatilhos DDL que ocorrem no momento da definição dos dados do banco, eventos esses que correspondem às instruções CREATE, ALTER e DROP.

Pela sua característica automática, a segurança na sua utilização e execução deve ser levada em consideração para evitarmos a utilização de forma mal-intencionada.


SINTAXE


Como em outras instruções vistas em artigos anteriores a sintaxe de construção pode mostrar várias opções, mas procuramos mostrar sempre uma forma mais simples visando os principiantes, caso desejem pode pesquisar as outras inúmeras opções existentes.

Para instruções DML (Insert, Update ou Delete)


CREATE TRIGGER < nome da trigger >
ON < tabela | visão >
FOR | AFTER | INSTEAD OF
[ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ]
AS
< Instruções de execução >

Para instruções DDL (Create, Alter ou Drop)


CREATE TRIGGER < nome da trigger >
ON { ALL SERVER | DATABASE }
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS
< Instruções de execução >

LIMITAÇÕES


Não inclua instruções SELECT que retornem resultados ou instruções que executem atribuição de variável em um gatilho. Um gatilho que inclua instruções SELECT que retornem resultados ao usuário ou instruções que executam atribuição de variável requer um tratamento especial;

Os resultados retornados devem ser gravados em todos os aplicativos nos quais as modificações na tabela de gatilhos são permitidas. Se uma atribuição de variável tiver de ocorrer em um gatilho, use a instrução SET NOCOUNT no início do gatilho para evitar o retorno de algum conjunto de resultados;

As instruções seguintes não podem ser utilizadas em gatilhos DML:


  • ALTER DATABASE
  • CREATE DATABASE
  • DROP DATABASE
  • RESTORE DATABASE
  • RESTORE LOG
  • RECONFIGURE
  • ALTER TABLE, quando adicionar, modificar ou deletar colunas; alternar partições e adicionar ou deletar chaves primárias ou únicas.

ANINHAMENTO DE GATILHOS


Gatilhos aninhados podem ser utilizados até o 32º nível. Recomenda o cuidado em sua utilização visando a performance do banco de dados

EXEMPLOS


Vamos criar uma tabela de log do estoque para o nosso banco, assim sempre que for incluído, alterado ou deletado algum registro, vamos guardar a informação.

Primeiro vamos à criação da tabela de log que vamos chamar de TBEstoqueLog.

CREATE TABLE TBEstoqueLog
(
      CD_EVENTO         INT         NOT NULL IDENTITY,
      CD_ESTOQUE        INT         NOT   NULL,
      DESC_OPERACAO     VARCHAR(10) NOT NULL,
      DESC_COLUNA       VARCHAR(30) NULL,
      VLR_ANTERIOR      VARCHAR(30) NULL,
      VLR_ATUAL         VARCHAR(30) NULL,
      DTH_OPERACAO      DATETIME    NOT NULL   
)

EXECUTE SP_ADDEXTENDEDPROPERTY 'MS_DESCRIPTION',
   'Tabela de Log dos Estoque',
   'USER', 'DBO', 'TABLE', 'TBEstoqueLog'
GO

ALTER TABLE TBEstoqueLog
   ADD CONSTRAINT PK_LOGE PRIMARY KEY (CD_EVENTO)
GO

Vamos ter uma coluna de código de controle da tabela, o código do estoque que está sendo alterado, qual a operação (insert, update ou delete), qual a coluna alterada, somente quando for update, o valor anterior e atual e a data que foi realizado.

Depois de criada a tabela vamos criar nossa TRIGGER:

CREATE TRIGGER TgEstLog ON TBEstoque
   AFTER INSERT,UPDATE,DELETE
AS
BEGIN

      DECLARE
            @Operacao   VARCHAR(15),
            @Coluna           VARCHAR(30),
            @VlrAnt           VARCHAR(15),
            @VlrAtu           VARCHAR(15)
     
      SET @Operacao = NULL
      SET @Coluna = ''
      SET @VlrAnt = ''
      SET @VlrAtu = ''
           
      --Verificando tipo do trigger
      IF EXISTS(SELECT 1 FROM inserted)
            SET @Operacao = 'INSERT'    
     
      IF EXISTS(SELECT 1 FROM deleted)
      BEGIN
            IF @Operacao IS NULL
                  SET @Operacao = 'DELETE'
            ELSE
                  SET @Operacao = 'UPDATE'
      END
     
     
      IF @OPERACAO = 'UPDATE'
      BEGIN
            IF UPDATE(QTD_ESTOQUE)
            BEGIN
                  SET @Coluna = 'QTD_ESTOQUE'
                  SET @VlrAnt = (SELECT QTD_ESTOQUE FROM Deleted)
                  SET @VlrAtu = (SELECT QTD_ESTOQUE FROM Inserted)
            END
           
            IF UPDATE(CD_UNIDADE)
            BEGIN
                  SET @Coluna = 'CD_UNIDADE'
                  SET @VlrAnt = (SELECT CD_UNIDADE FROM Deleted)
                  SET @VlrAtu = (SELECT CD_UNIDADE FROM Inserted)
            END
           
            IF UPDATE(VLR_UNITARIO)
            BEGIN
                  SET @Coluna = 'VLR_UNITARIO'
                  SET @VlrAnt = (SELECT VLR_UNITARIO FROM Deleted)
                  SET @VlrAtu = (SELECT VLR_UNITARIO FROM Inserted)
            END
      END
     
      IF EXISTS(SELECT 1 FROM inserted)
      begin
            INSERT INTO TBEstoqueLog (CD_ESTOQUE, DESC_OPERACAO, DESC_COLUNA, VLR_ANTERIOR, VLR_ATUAL, DTH_OPERACAO)
            SELECT
                  CD_ESTOQUE,
                  @Operacao,
                  @Coluna,
                  @VlrAnt,
                  @VlrAtu,
                  getdate()
            FROM Inserted
      end
     
      IF @OPERACAO = 'delete'
      begin
            INSERT INTO TBEstoqueLog (CD_ESTOQUE, DESC_OPERACAO, DESC_COLUNA, VLR_ANTERIOR, VLR_ATUAL, DTH_OPERACAO)
            SELECT
                  CD_ESTOQUE,
                  @Operacao,
                  @Coluna,
                  @VlrAnt,
                  @VlrAtu,
                  getdate()
            FROM deleted
      end

END

Testamos qual a operação realizada:

--Verificando tipo do trigger
      IF EXISTS(SELECT 1 FROM inserted)
            SET @Operacao = 'INSERT'    
     
      IF EXISTS(SELECT 1 FROM deleted)
      BEGIN
            IF @Operacao IS NULL
                  SET @Operacao = 'DELETE'
            ELSE
                  SET @Operacao = 'UPDATE'
      END
     

Buscamos os valores anterior e atual quando a operação for de update:

      IF @OPERACAO = 'UPDATE'
      BEGIN
            IF UPDATE(QTD_ESTOQUE)
            BEGIN
                  SET @Coluna = 'QTD_ESTOQUE'
                  SET @VlrAnt = (SELECT QTD_ESTOQUE FROM Deleted)
                  SET @VlrAtu = (SELECT QTD_ESTOQUE FROM Inserted)
            END
           
            IF UPDATE(CD_UNIDADE)
            BEGIN
                  SET @Coluna = 'CD_UNIDADE'
                  SET @VlrAnt = (SELECT CD_UNIDADE FROM Deleted)
                  SET @VlrAtu = (SELECT CD_UNIDADE FROM Inserted)
            END
           
            IF UPDATE(VLR_UNITARIO)
            BEGIN
                  SET @Coluna = 'VLR_UNITARIO'
                  SET @VlrAnt = (SELECT VLR_UNITARIO FROM Deleted)
                  SET @VlrAtu = (SELECT VLR_UNITARIO FROM Inserted)
            END
      END

Inserimos em nossa tabela de log de acordo com a operação realizada;

      IF EXISTS(SELECT 1 FROM inserted)
      begin
            INSERT INTO TBEstoqueLog (CD_ESTOQUE, DESC_OPERACAO, DESC_COLUNA, VLR_ANTERIOR, VLR_ATUAL, DTH_OPERACAO)
            SELECT
                  CD_ESTOQUE,
                  @Operacao,
                  @Coluna,
                  @VlrAnt,
                  @VlrAtu,
                  getdate()
            FROM Inserted
      end
     
      IF @OPERACAO = 'delete'
      begin
            INSERT INTO TBEstoqueLog (CD_ESTOQUE, DESC_OPERACAO, DESC_COLUNA, VLR_ANTERIOR, VLR_ATUAL, DTH_OPERACAO)
            SELECT
                  CD_ESTOQUE,
                  @Operacao,
                  @Coluna,
                  @VlrAnt,
                  @VlrAtu,
                  getdate()
            FROM deleted
      end

END

Vamos testar a nossa trigger:

INSERT INTO TBESTOQUE (CD_PRODUTO, QTD_ESTOQUE, CD_UNIDADE, VLR_UNITARIO, DTH_MOVIMENTO) VALUES (12,1,7,2.50,GETDATE())

UPDATE TBESTOQUE
SET QTD_ESTOQUE = 2
WHERE CD_ESTOQUE = 13

DELETE FROM TBESTOQUE WHERE CD_ESTOQUE = 13

E o resultado da execução da trigger é o mostrado abaixo:

CD_EVENTO   CD_ESTOQUE  DESC_OPERACAO DESC_COLUNA                    VLR_ANTERIOR                   VLR_ATUAL                      DTH_OPERACAO
----------- ----------- ------------- ------------------------------ ------------------------------ ------------------------------ -----------------------
22          21          INSERT                                                                                                     2013-08-22 11:00:46.060
23          21          UPDATE        QTD_ESTOQUE                    1.0000                         2.0000                         2013-08-22 11:01:00.440
24          21          DELETE                                                                                                     2013-08-22 11:01:03.363

(3 row(s) affected)


Com um exemplo prático mostramos como funcionam as TRIGGERS, espero que tenha ficado claro, qualquer dúvida, é só comentar.

Com essa série de artigos procuramos mostrar um pouco da linguagem SQL, como funcionam as operações via código, espero que tenham gostado, e brevemente estaremos de volta com mais novidades.

Grande abraço e até breve.




Comentários

Postagens mais visitadas