Espaço da N.B.T.: Trigger -- Quero aprender a fazer uma!!!

Boa Noite queridos compatriotas!!! Mais uma vez aqui unidos, juntos, misturados para mais uma aventura pelo mundo do SQL. Para vocês trago a famosa Trigger. Com alguns exemplos postos em questão fica muito mais fácil de compreender. As Triggers são conhecidas como Gatilhos. Esse nome é definido assim pois é disparado um comando a partir do momento em que foi usado um insert, um delete ou mesmo um update. Como isso?? Um exemplo simples, apenas a título de conhecimento, imaginemos que por um acaso, em uma bela manhã ensolarada, acordei e resolvi fazer um insert. Ótimo, mas ao mesmo tempo que tomar alguns dados e inserílos em outra entidade (tabela), porém não quero todo o insert e sim alguns dados apenas e com algumas condições. Ah eu até poderia fazer vários Inserts em todas as entidades... Mas o problema é "e se de repente eu resolva fazer um novo insert e outro e mais outro, inúmeros inserts e que todos tenham exijam um parâmetro, um parâmetro responsável por enviar ou não tais dados a outras tantas entidades?", para isso precisamos de algo que faça esse serviço. Vamos começar então.

Criando o ambiente

Ótimo já chegamos até aqui. Se você teve paciência e foi vitorioso(a), vamos preparar o ambiente. Todos estão com o SQL Server aí?? Quem não pode buscá-lo vamos precisar.
Conecte-se e crie um banco:

CREATE DATABASE bd_Trigger
USE bd_Trigger

O próximo passo é criar as entidades. Por elas criaremos toda a ordem de negócio, simples por sinal. A propósito vou dedicar um post com uma conversa sobre modelagem, muito útil.
Bom voltando iremos criar quatro entidades: Produto, LogPreco, PedidoCompra, ProdutosExcluidos.

Veja:

CREATE TABLE ProdutosExcluidos(

cd_Produto INT NULL,

ds_produto VARCHAR(50) NULL,

dt_exclusao SMALLDATETIME NULL)

CREATE TABLE Produto(

cd_Produto INT NOT NULL,

ds_Produto VARCHAR(50) NOT NULL,

vl_Produto MONEY NULL,

qt_Estoque INT NULL,

qt_Estoquemin INT NULL,

CONSTRAINT PK_Produto PRIMARY KEY CLUSTERED(cd_Produto))

CREATE TABLE PedidoCompra(

cd_PedidoCompra INT IDENTITY NOT NULL,

cd_Produto INT NOT NULL,

dt_Solicitacao SMALLDATETIME NOT NULL,

qt_Compra INT NOT NULL,

CONSTRAINT PK_PedidoCompra PRIMARY KEY CLUSTERED (cd_PedidoCompra))

CREATE TABLE LogPreco(

cd_LogPreco INT IDENTITY NOT NULL,

dt_Alteracao SMALLDATETIME NOT NULL,

cd_Produto INT NOT NULL,

vl_ProdutoAntigo MONEY NOT NULL,

vl_ProdutoNovo MONEY NOT NULL,

CONSTRAINT PK_LogPreco PRIMARY KEY CLUSTERED (cd_LogPreco))

Ótimo. Agora vamos fazer alguns relacionamentos. Não havia feito antes porque acho muito mais interessante fazê-lo já tendo as tabelas construídas. É uma forma de visualizar o que possuo antes de sair disparando relacionamentos. Veja:

ALTER TABLE LogPreco
ADD CONSTRAINT fk_LogPreco
FOREIGN KEY (cd_Produto)
REFERENCES Produto (cd_Produto)

ALTER TABLE PedidoCompra
ADD CONSTRAINT fk_PedidoCompra
FOREIGN KEY (cd_Produto)
REFERENCES Produto (cd_Produto)

Após tudo estar preparado vamos para a parte que todos esperavam.. a Trigger.

Vamos começar com uma trigger que deverá ser disparada nas operações de UPDATE, o que significa que quando fizermos um update ela automaticamente irá disparar valores. Como assim? Vamos supor que eu queira alterar o valor de alguns preço na tabela de Produto e ainda assim queira manter um registro dos valores antigos. Ao invés de fazer a alteração e depois inserir manualmente o que seria uma perca de tempo e poderia até gerar a inconsistência do dado ou mesmo a não inserção do valor alterado na tabela de histórico, a trigger automaticamente faria esse trabalho sujo (sahuasu). Então a ideia é fazer um trigger ligando a tabela Produto e LogPreco. Vamos deixar claro que cd_logpreco é autonumeração, ou seja não requer digitação, aliás nem é possível tal coisa, isso significa que não deve constar na lista de campos quando a trigger for executar o insert na tabela Log.

Primeiro vamos inserir alguns valores:

INSERT Produto([cd_Produto], [ds_Produto] ,  [vl_Produto] , [qt_Estoque] , [qt_Estoquemin]) VALUES ( 1 , 'Prego' , 1.0000 , 25 , 10)
INSERT Produto ( [cd_Produto] , [ds_Produto] , [vl_Produto] , [qt_Estoque] , [qt_Estoquemin] )  VALUES ( 2 , 'Martelo' , 50.0000, 30 , 15)
INSERT Produto ( [cd_Produto] , [ds_Produto] , [vl_Produto] , [qt_Estoque] , [qt_Estoquemin] )  VALUES ( 3 , 'Parafuso' , 2.0000 ,  10 ,  5)
INSERT Produto ( [cd_Produto] , [ds_Produto] , [vl_Produto] , [qt_Estoque] , [qt_Estoquemin] )   VALUES ( 4 , 'Chave de Fenda' , 25.0000 , 15 , 10)

Agora faça os testes a seguir:

SELECT * FROM LogPreco
SELECT * FROM Produto

Este é o resultado que deve ser visualizado:

Você vai poder colar tudo se precisar na query, já que os comentários estão..comentados!

--Agora vamos criar a Trigger:

CREATE TRIGGER tr_MudancaValor ON Produto FOR UPDATE

--Vamos comentar essa primeira linha. Aqui a ideia é fazer um gatilho que

--dispare em um Update, certo? Então repare que utilizamos "FOR UPDATE" ou seja para um update, em uma tradução grosseira.

--Nesse momento vamos criar variáveis. A função de cada variável vai variar, literalmente da

--variação da nossa necessidade com relação toda a regra de negócio. Repare a

--indispensabilidade em informar o tipo da variável. São iguais as dos campos que vão atribuir

--os valores. Verá logo o porque.

DECLARE @dt_alteracao SMALLDATETIME

DECLARE @cd_produto INT

DECLARE @vl_produtoant MONEY

DECLARE @vl_produtonovo MONEY

--Algo que talvez você não conheça, mas será apresentado nesse momento. Pois bem, cada vez

--que você faz um Update, duas tabelas temporárias, de uso do banco, mas que nós muitos

que --elas fazem, ou melhor dizendo armazenam (temporariamente, importante frizar) é os novo

--espertos iremos usufruir desse bem, ssão criadas. Elas se chamam Inserted e Deleted. O
s --valores e os antigos, nesta oredem. Nesse caso o que você tinha gravado, temporáriamente está

nca, jamais, never, ouviu, never, diga que além dessas duas tabelas existe --uma cham

--na tabele Deleted e o que você resolveu alterar está em Inserted. Pesso agora muita --atenção, n

uada Update, está me lendo?? Continuando...

--Repare que as variáveis @cd_produto e @vl_produtoant recebem os valores de cd_Produto

--e vl_Produto, nesta ordem, de Deleted. O que significa que nesse momento estou usando

--do poderoso recurso de coletar dos antigos registros (momentaneos) da tabela Deleted.

SELECT @cd_produto=cd_Produto,@vl_produtoant=vl_Produto FROM DELETED

--Faço o mesmo com a tabela Inserted para a variável @vl_produtoant. A diferença é

--para @dt_alteracao que utilizo a função GETDATE(), para recuperar a data da "alteração".

SELECT @dt_alteracao = GETDATE() , @vl_produtoant = vl_Produto FROM INSERTED

--No final inserimos todos os valores dessas vaiáveis nos campos da tabela LogPreco e temos

--uma inserção automática e com valores que custariam muito tempo para buscálos, analisá-los

--e inserí-los manualmente.

INSERT INTO LogPreco VALUES

(@dt_alteracao,@cd_produto , @vl_produtoant , @vl_produtoant)

Vamos fazer um teste antes de executar a Trigger:

SELECT * FROM LogPreco

Esse deve ser o resultado:

Facinante, não? Também achei. Bom agora o momento que todos esperavam... Vamos testar a Trigger!

UPDATE Produto SET vl_Produto*=1.50

"Mas... você disse que íamos testar a Trigger e não fazer um Update!"

Sim, e nós testamos! Claro que muitos "mas..." devem estar surgindo em sua cabeça, mas é assim mesmo. Lembra que a ideia de um gatilho é justamente essa (deste ao menos) "Ao fazer um Update, que certos dados sejam armazenados automaticamente na tabela LogPreco", então foi o que fizemos, o resultado é esse: