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:
(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.
