Upload
others
View
1
Download
0
Embed Size (px)
Citation preview
Banco de Dados – III Prof. Gilberto Braga de Oliveira
1
Atividade II: Trigger – (Gatilho)
Competências: 1. Otimizar a linguagem de consulta estruturada como forma
de informação relevante para a tomada de decisão. Habilidades: 1.1 Executar
linguagem de consulta estruturada objetivando melhor desempenho; 1.2 Compilar
relatórios analíticos a partir dos dados coletados. Bases Tecnológicas: Blocos de
linguagem de consulta estruturada (SQL); Funções; Visões Controladas.
Obs: É recomendável acessar a página do professor na internet e baixar os arquivos
usados nesta atividade (ddlOficinaMecanica, dmlInsert_OficinaMecanica e triggger_Teste.sql),
disponibilizados em um arquivo compactado OficinaMecanica_Transact.zip em
<https://drive.google.com/file/d/1krOnYC4OvNojYvyyKUADIXIXShN3fXrF/view?usp=sharing>,
para melhorar o foco no objeto de estudo, que é a criação e testes de trigger. Entretanto, se for
possível copiar e colar ou escrever diretamente no SQL Server Management Studio os códigos
apresentados a seguir, também será possível acompanhar esta aula, mas de forma um pouco
mais morosa.
Obs2: Recomenda-se criar cada trigger em um arquivo individual; Todo o código de teste pode
estar em um único arquivo.
1. Acesse o arquivo ddlOficinaMecanica.sql e execute o script inteiro para a criação do
banco de dados em questão, observe as tabelas específicas de transações adicionadas
no código anterior conforme destaque a seguir:
USE master; GO DROP DATABASE OficinaMecanica; GO --Criação do banco de dados CREATE DATABASE OficinaMecanica; GO -- Usando o banco de dados Oficina Mecanica USE OficinaMecanica; GO -- Criação dos esquemas necessários CREATE SCHEMA Oficina; GO --Criação da tabela Revisao CREATE TABLE Oficina.Revisao( CodRevisao INT NOT NULL IDENTITY, Placa CHAR(7), CREA CHAR(5), DataHora DATETIME NOT NULL, -- Atualizado Transact ValorTotal MONEY, -- Atualizado Transact Relatorio VARCHAR(1000), TrocaPecas BIT
Banco de Dados – III Prof. Gilberto Braga de Oliveira
2
); GO --Criação da chave primária da tabela Revisão ALTER TABLE Oficina.Revisao ADD CONSTRAINT PK_Revisao PRIMARY KEY(CodRevisao); GO --Criação de um índice no campo DataHora ordenado de forma descrescente CREATE INDEX IDX_DataHora ON Oficina.Revisao(DataHora DESC); -- Atualizado Transact GO -- PARTE TRANSACIONAL CREATE TABLE Oficina.Item( CodItem INT NOT NULL IDENTITY, Descricao VARCHAR(50) NOT NULL, ValorUnitario MONEY, QuantidadeEstoque INT, CONSTRAINT PK_Item PRIMARY KEY(CodItem) ); GO CREATE TABLE Oficina.OrdemServico( CodRevisao INT NOT NULL, CodItem INT NOT NULL, Quantidade INT NOT NULL, CONSTRAINT PK_OrderServico PRIMARY KEY(CodRevisao, CodItem), CONSTRAINT FK_Item_OrdemServico FOREIGN KEY(CodItem) REFERENCES Oficina.Item(CodItem), CONSTRAINT FK_Revisao_OrdemServico FOREIGN KEY(CodRevisao) REFERENCES Oficina.Revisao(CodRevisao) ); GO
Banco de Dados – III Prof. Gilberto Braga de Oliveira
3
2. Gere o diagrama que demonstra as tabelas e as ligações entre elas.
3. No script de inserção dmlInsert_OficinaMecanica.sql, alguns registros serão inseridos
em cada uma das tabelas do banco de dados, inclusive nas tabelas revisão e item,
contidas no schema oficina; Os campos valor total e troca de peças de revisão e o valor
unitário de cada item serão cadastrados previamente e manipulados ao longo do
execício. Obs.: Se necessário, use o comando select para ver o conteúdo da tabela antes
de prosseguir.
USE OficinaMecanica; GO /* EXEC sp_help 'Oficina.Revisao'; GO SELECT * FROM Oficina.Revisao; GO */
Item (Oficina)
CodItem
Descricao
ValorUnitario
QuantidadeEstoque
OrdemServico (Oficina)
CodRevisao
CodItem
Quantidade
Revisao (Oficina)
CodRevisao
Placa
CREA
DataHora
ValorTotal
Relatorio
TrocaPecas
Banco de Dados – III Prof. Gilberto Braga de Oliveira
4
-- Inserindo dados na tabela Revisão ATUALIZADO PARA O MODELO TRANSACIONAL INSERT INTO Oficina.Revisao(Placa, CREA, DataHora, ValorTotal, Relatorio, TrocaPecas) VALUES('EFG5678', '12345', '2014-18-04 10:15', 315.25, 'Revisão geral do auto, troca de óleo e alinhamento', 0); GO INSERT INTO Oficina.Revisao(Placa, CREA, DataHora, ValorTotal, Relatorio, TrocaPecas) VALUES('BCD2345', '45678', '2014-18-04 08:28', 200.25, 'Alinhamento e balanceamento', 0); GO INSERT INTO Oficina.Revisao(Placa, CREA, DataHora) VALUES('DEF4567', '23456', '2014-18-04 09:42'); GO INSERT INTO Oficina.Revisao(Placa, CREA, DataHora) VALUES('CDE3456', '12345', '2014-18-04 15:25'); GO INSERT INTO Oficina.Revisao(Placa, CREA, DataHora) VALUES('DEF4567', '34567', '2014-18-04 14:20'); GO -- Inserindo dados na tabela Item /* EXEC sp_help 'Oficina.Item'; GO SELECT * FROM Oficina.Item; GO */ INSERT INTO Oficina.Item(Descricao, ValorUnitario, QuantidadeEstoque) VALUES('Pneu A', 220.0, 84); GO INSERT INTO Oficina.Item(Descricao, ValorUnitario, QuantidadeEstoque) VALUES('Pneu B', 260.0, 35); GO INSERT INTO Oficina.Item(Descricao, ValorUnitario, QuantidadeEstoque) VALUES('Pneu C', 180.0, 90); GO
Banco de Dados – III Prof. Gilberto Braga de Oliveira
5
INSERT INTO Oficina.Item(Descricao, ValorUnitario, QuantidadeEstoque) VALUES('Óleo de Motor W50', 25.0, 25); GO INSERT INTO Oficina.Item(Descricao, ValorUnitario, QuantidadeEstoque) VALUES('Óleo de Motor W30', 30.0, 15); GO INSERT INTO Oficina.Item(Descricao, ValorUnitario, QuantidadeEstoque) VALUES('Pastilha de Freio', 40.0, 77); GO -- Veja as tabelas SELECT * FROM Oficina.Item; GO
Figure 1 - Tabela de itens ainda não vendidos.
SELECT CodRevisao, ValorTotal, TrocaPecas FROM Oficina.Revisao; GO
Figure 2 - Tabela de revisão, ainda sem troca de peças.
Banco de Dados – III Prof. Gilberto Braga de Oliveira
6
4. Crie um arquivo SQL novo para a implementar a trigger de inserção de ordens de serviço.
Nome sugerido: trigger_Insert_OS.sql. Esta trigger deverá 1. atualizar do valor total da
revisão atribuindo ao campo o produto (MULTIPLICAÇÃO) da quantidade de itens a
serem inseridos nesta tabela (ordem de serviço) e o valor unitário do item inserido,
conforme tabela item; 2. Atualize também o campo troca de peças da mesma tabela
(revisão) atribuindo 1 a este campo, indicando assim que houve troca de peças, sim. E
3. finalmente, a quantidade de itens em estoque, campo da tabela item, também deve
ser devidamente atualizada com a diferença entre a quantidade de itens antigos e os
que foram incluídos na ordem de serviço. Obs.: Pode ser que usar o campo valor total
da tabela revisão como acumulador não funcione, neste caso, crie uma estrutura de
decisão que permita verificar se o campo é null antes e, caso seja, atribua o valor
diretamente ao campo, caso não seja, utilize-o como acumulador. Segue o código de
teste:
-- Atividade TRIGGER USE OficinaMecanica; GO -- Preenchendo a tabela de Ordem de Serviços três produtos para a revisão 3 INSERT INTO Oficina.OrdemServico VALUES(3, 1, 2); GO INSERT INTO Oficina.OrdemServico VALUES(3, 6, 2); GO INSERT INTO Oficina.OrdemServico VALUES(3, 3, 2); GO -- Preenchendo a tabela de Ordem de Serviços dois itens para a revisão 4 INSERT INTO Oficina.OrdemServico VALUES(4, 4, 4); GO INSERT INTO Oficina.OrdemServico VALUES(4, 6, 2); GO
Banco de Dados – III Prof. Gilberto Braga de Oliveira
7
-- Preenchendo a tabela de Ordem de Serviços dois itens para a revisão 5 INSERT INTO Oficina.OrdemServico VALUES(5, 4, 4); GO INSERT INTO Oficina.OrdemServico VALUES(5, 1, 1); GO -- Preenchendo a tabela de Ordem de Serviços um produto para a revisão 1 INSERT INTO Oficina.OrdemServico VALUES(1, 5, 5); GO -- Veja as tabelas SELECT
CodRevisao [Revisao], Descricao, ValorUnitario [V. Unit.], Quantidade [Qtd], ValorTotal [V. Total], QuantidadeEstoque [Estoque]
FROM Oficina.vItemOSRevisao ORDER BY CodRevisao; GO
Figure 3 - Todos os registros inseridos na tabela de Ordem de Serviço.
Banco de Dados – III Prof. Gilberto Braga de Oliveira
8
5. Crie um arquivo SQL novo para a implementar a trigger de exclusão de ordens de
serviço. Está trigger fará o oposto do que fez a trigger anterior. Nome sugerido:
trigger_delete_OS.sql (ou qualquer coisa equivalente). Esta trigger deverá 1. atualizar
do valor total da revisão atribuindo ao campo o produto (MULTIPLICAÇÃO) da
quantidade de itens a serem excluídos nesta tabela (ordem de serviço) e o valor
unitário do item excluído, conforme tabela item; 2. Atualize também o campo troca de
peças da mesma tabela (revisão) atribuindo 0 a este campo, indicando assim que – caso
todos os itens tenham sido excluídos desta revisão – NÃO houve troca de peças. E 3.
finalmente, a quantidade de itens em estoque, campo da tabela item, também deve ser
devidamente atualizada com a diferença entre a quantidade de itens antigos e os que
foram excluídos na ordem de serviço. Obs.: Para saber se resta algum item na ordem de
serviço para a mesma revisão basta contar o código da revisão na tabela ordem de
serviços; se o resultado for 0 (zero) é porque todos os itens foram removidos e,
portanto, para esta revisão, não houve troca de peças. Observe o código de teste a
seguir:
-- Ao excluindo os itens da ordem de serviço da revisão 1 ela desaparece do resultado DELETE FROM Oficina.OrdemServico WHERE CodRevisao = 1 AND CodItem = 5; GO SELECT CodRevisao, ValorTotal, TrocaPecas FROM Oficina.Revisao; GO
Figure 4 - O cliente mudou de ideia, não vai mais comprar o óleo de motor.
Banco de Dados – III Prof. Gilberto Braga de Oliveira
9
-- Devolver as pastilhas de freio da revisão 4 DELETE FROM Oficina.OrdemServico WHERE CodRevisao = 4 AND CodItem = 6; GO -- Repare: a quantidade de pastilhas de freio aumentou, de 73 para 75, e o valor total da revisão diminuiu de 180 para 100 SELECT
CodRevisao [Revisao], Descricao, ValorUnitario [V. Unit.], Quantidade [Qtd], ValorTotal [V. Total], QuantidadeEstoque [Estoque]
FROM Oficina.vItemOSRevisao ORDER BY CodRevisao; GO
Banco de Dados – III Prof. Gilberto Braga de Oliveira
10
6. Crie um arquivo SQL novo para a trigger de atualização de ordens de serviço. Está trigger
é um pouco mais simples, pois trata apenas dois problemas (e não três) como as
anteriores. Nome sugerido: trigger_update_OS.sql (ou qualquer coisa assim). Esta
trigger deverá verificar se será feita uma atualização no campo quantidade da tabela
ordem de serviço, se sim: 1. atualizar o valor total da revisão atribuindo ao campo o
produto (MULTIPLICAÇÃO) da quantidade de itens pela diferença entre a quantidade
inserida e a quantidade excluída de itens na tabela ordem de serviço. E 2. atualizar a
quantidade de itens em estoque, campo da tabela item, atribuindo a diferença entre a
quantidade de itens inseridos em relação a quantidade de itens excluídos na ordem de
serviço. Obs.: Criar uma view para acompanhar a atualização dos valores nas três tabelas
pode facilitar muito o trabalho. Sinta-se à vontade. Observe o código de teste a seguir:
-- O Cliente da revisão 3 resolveu comprar mais dois pneus A ... UPDATE Oficina.OrdemServico SET Quantidade = 4 WHERE CodItem = 1 AND CodRevisao = 3; GO -- ...e levar só um Pneu C para o step. UPDATE Oficina.OrdemServico SET Quantidade = 1 WHERE CodItem = 3 AND CodRevisao = 3; GO
-- Confira o valor total. Compare as quantidades em estoque atualizadas pela devolução. SELECT
CodRevisao [Revisao], Descricao, ValorUnitario [V. Unit.], Quantidade [Qtd], ValorTotal [V. Total], QuantidadeEstoque [Estoque]
FROM Oficina.vItemOSRevisao ORDER BY CodRevisao; GO
Banco de Dados – III Prof. Gilberto Braga de Oliveira
11
Figure 5 - Devolução de Pneus.
Banco de Dados – III Prof. Gilberto Braga de Oliveira
12
-- O cliente da revisão 1 voltou atrás e resolveu levar o óleo de motor INSERT INTO Oficina.OrdemServico VALUES(1, 5, 5); GO
Figure 6 - O cliente da revisão 1 se arrependeu e voltou prá buscar o óleo de motor.