Desvendando o SQL - Parte XI - Gatilhos
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.
Artigos anteriores:
Comentários