Trigger para Delete

Caso você não tenha lido "Trigger -- Quero aprender a fazer uma!!!" é só clicar para dar uma conferida. Mas como eu sei que provavelmente você não irá fazer isso então eu ofereço para você o ambiente:


CREATE DATABASE bd_Trigger
USE bd_Trigger

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))

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)

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)


Após todo os preparativos vamos esclarecer qual o sentido dessa trigger. A ideia é simples: A trigger vai ser disparada nas opções de DELETE. Quando isso acontecer um registro será gravado para cada produto excluído na tabela ProdutosExcluidos, criando então um log.


Faça os testes a seguir pra termos certeza que as tabelas necessárias para a atuação das triggers resultarem em sucesso:

SELECT * FROM ProdutosExcluidos

SELECT * FROM Produto

Os resultados devem ser esses:

Agora iremos construir a trigger. Você poderá colar tudo em um query, mas aconselho que leia e entenda, isso é muito importante e será muito útil futuramente.

--Criando uma Trigger para deletes

CREATE TRIGGER tr_ExclusaoProduto ON Produto FOR DELETE

--Agora as variáveis responsáveis por toda a mágia

AS

DECLARE @cd_Produto INT

DECLARE @ds_Produto VARCHAR(50)

DECLARE @dt_Exclusao SMALLDATETIME

--Atribuindo valores da tabela deleted para as variáveis  equivalentes

SELECT @cd_Produto=cd_Produto,@ds_Produto = ds_Produto , @dt_Exclusao  = GETDATE() FROM deleted

--Com esses valores já disponíveis, faço uma inserção na tabela ProdutosExcluidos

INSERT INTO ProdutosExcluidos 

VALUES ( @cd_Produto , @ds_Produto , @dt_Exclusao) 

Agora pode executar.

Para testarmos a trigger iremos fazer um teste. Suponhamos que o produto Martelo foi tirado das prateleiras, ou seja, não será mais vendido. Obviamente  teremos de excluir tal item do banco. Nesse caso ao fazermos:

DELETE FROM Produto WHERE cd_Produto=2


(código 2, pois esse é o código do martelo, visivelmente apresentado no resultado do teste antes de construir a trigger)


O resultado deve ser esse:

Por que duas linhas separadamente afetadas? A resposta é simples! Faça os seguintes teste e veja os resultados:



SELECT * FROM Produto
SELECT * FROM ProdutosExcluidos

Veja que o item "Martelo" Desapareceu da tabela "Produto" e em compensação surgiu um registro "Martelo" em "ProdutosExcluidos". Tudo isso foi possível por conta da Trigger criada anteriormente.

Ir para "Como faço uma Trigger de Inserção e Update??"

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.

AS

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:

Repare que 1 e 4 linhas foram afetadas, 4 alterações e 1 que você entenderá porque após esse teste:

SELECT * FROM LogPreco

Resultado:

Faça outros testes de Update... e veja os resultados! Agora deixo vocês e aguardem o próximo post: "Trigger para Delete". Até mais ^^ o/

Criando Cursor em SQLServer

Faz nem muito tempo estava eu procurando feito uma doida como fazer um cursor em SQL. Encontrei muita coisa, mas como não tive muita facilidade com os exemplos e a sintaxe cruamente exposta, achei interessante postar aqui o que fiz para poder entregar no TP de Banco de Dados que tive de fazer para a faculdade. Já entreguei o TP, mas se alguém tiver uma sugestão para melhorá-lo é sempre bom.

Fazendo Cursor

Primeiro o que é uma cursor? É algo bem simples mesmo no banco de dados existe uma área de memória, que tem o poder de armazenar linhas feitas em um Select. Que linhas são essas? Não tem quando você faz um Select ele apresenta uma sequência de registros, então cada registro desse é uma linha. Voltando um cursor sempre será criado com uma função Select, exatamente pelo motivo explicado acima.. Mesmo que você não saiba, quando você está fazendo um Select a diferença é que você não consegue manipulá-lo. Vamos domá-lo então haha.

Como eu usei uma trigger para criar o cursor vou explicar passo-a-passo:

Primeiro vamos criar a trigger:

create trigger tr_mudancavalor_cursor

on produto

for update <-- Com isso você informa que a Trigger só será executada quando houver um Update

as

Onde tr_mudancavalor_cursor é o nome da trigger e produto e o nome da tabela da qual você pretende que ao fazer um update, a Trigger seja executada.

Agora declaro os ponteiros:

declare p_p Cursor for Select cd_produto,vl_produto from inserted

declare p_p2 Cursor for select vl_produto from deleted

Explicarei agora:

O primeiro e importantíssimo passo é declarar o cursor, se não lógica mente ele não existirá (hiihi). Você vê também p_p que é o nome do cursor. Cursor para o select (essa seria a tradução a grosso modo de Cursor (que é palavra do sistema) for select) dos campos cd_produto, vl_produto da tabelainserted. Viu que legal você pode tirar dados de campos específicos. Inserted é a tabela temporária criada pelo próprio banco quando você faz um insert, um delete ou mesmo o update. A mesma coisa acontece com deleted, que aparece na linha abaixo, também uma declaração de cursor, apenas tendo como diferença que só e criada quando existe um delete ou update.

Prosseguiremos com Trigger normalmente:

--declarando variáveis

declare @CD_PRODUTO INT

declare @qt_estoque int

declare @qt_estoquemin int

declare @vl_novo money

declare @ds_produto varchar(50)

declare @vl_antigo money

Para saber:

declare @vl_antigo(nome da variável) money(tipo da variável, no caso é valor para dinheiro)

Continuando..

Os ponteiros para funcionarem precisam ser abertos:

--abrindo ponteiros

open p_p

open p_p2

Então faço as condições de inserção:

--condições para iserção

begin

--preciso dos valores já para compará-lo, isso quer dizer que está ocorrendo uma alteração

      fetch p_p into @CD_PRODUTO,@vl_novo

      fetch p_p2 into @vl_antigo

      while @@FETCH_STATUS=0

      begin

            fetch p_p into @CD_PRODUTO,@vl_novo

            fetch p_p2 into @vl_antigo

--para saber se o valor não é o mesmo

            if @vl_antigo <> @vl_novo

            begin

                  insert into logpreco (dt_alteracao,cd_produto,vl_produtoant,vl_produtonovo)

                  values (getdate(),@CD_PRODUTO,@vl_antigo, @vl_novo)

            end

      end

O que vem a ser fetch.. Bom vai ser uma informação rápida e indolor: Você vai pegar o valor do ponteiro (p_p ou mesmo p_p2) nos determinados campos desejados que está apontado para o primeiro registro do select e enfiar, é isso mesmo, enfiar nas variáveis(@CD_PRODUTO,@vl_novo ou para p_p2 @vl_antigo) que você criou.

Tá o que eu faço com essa informação?? Agora com esses valores você pode trabalhar cada um deles. No meu caso Eu fui fazer um reajuste de preços na minha tabela de preços, e optei por reajustar todos os preços pelo mesmo novo preço. E quando eu fizer isso a minha Trigger vai colocar os registros dessas alterações em uma tabela de log. Sabe, já testei sem cursor e ao invés de surger o mesmo número de registros alterados, apenas um aparecia, o último a ser analisado pelo select. Então para resolver o problema fiz o seguinte: Criei os dois ponteiros, para permitir que o select seja varrido registro por registro. Tá por que estou frisando isso?? Porque estou querendo dizer que você também poderio usa um loop, mas é bem mais pratico um cursor. Repare no comentários em verde em cima dos fetch. Eles explicam tudo. Repare no comentário embaixo deles eu não queira adicionar registros os quais não tenham sofrido alteração. Opa, mas como isso?? Você não disse que fez alteração no preço de todo mundo?? Sim, sim fiz, mas o que acontece, quem me garante que já não existem preços iguais. Ei, você não explicou uma coisa o que é @@FETCH_STATUS?? Não sei se você percebeu, mas esse tal aí vem acompanhado com o while. Então eu fiz um loop, por quê? Porque enquanto o cursor tiver correndo eu quero saber quando ele vai chegar a 0. Porque zero vai mostrar que não existe mais registros no select. Quando isso acontecer vai sair do while.O insert intologpreco que você vê é para colocar na minha tabela de registros os tais valores que capturei com os meus ponteiros (eles são tão fofuchinhos =** ).

--fechando ponteiros

close p_p

close p_p2

--desalocando os ponteiros

deallocate p_p

deallocate p_p2

end

Assim como você abre o ponteiro é preciso fechá-lo.

O dellocate desaloca o ponteiro. 

Quero comentar que begin e end, trabalham como se fosse {} e getdate() recupera a data do dia, no caso do dia em que o registro foi inserido na tabela de registros. 

Ei, gente espero que tenham gostado. Qualquer mandem sugestões para nathalia_electron@hotmail, com o título da mensagem "cursor - SQLServer".

Xau

Bjins !*

Até a próxima ^^