[SQL Server] Exame 70 461 – Tópico 2: Criando e alterando views – Parte 3
Publicado; 02/09/2012 Arquivado em: Exame 70-461 | Tags: exame 70-461, exame sql server 2012 Deixe um comentárioViews Atualizáveis (Updatable Views)
É possível através de uma view fazermos modificações nos dados da tabela base referenciada. Em outras palavras, podemos executar instruções UPDATE, DELETE e INSERT diretamente contra a view. Este recurso aumenta a granularidade da segurança, algo que estaremos abordando em tópico futuro. Contudo, executar modificações de dados diretamente sobre uma view é algo que possui algumas restrições:
· A modificação não pode afetar mais que uma tabela base: ou a view, em sua instrução SELECT, referencia apenas uma única tabela, ou a view possui uma trigger do tipo instead of;
· A modificação não pode afetar colunas agregadas;
· A modificação precisa preencher as colunas NOT NULL da tabela base.
Analisemos um exemplo.
use DBExame70461
go
— 1) Inicialmente criar duas tabelas: Categoria e Produto.
CREATE TABLE Categoria
( ID INT NOT NULL PRIMARY KEY,
Descricao VARCHAR(50) NOT NULL
);
CREATE TABLE Produto
( ID INT NOT NULL PRIMARY KEY,
CategoriaID INT NOT NULL,
Nome VARCHAR(50) NOT NULL,
Preco DECIMAL(15,2) NULL
);
— 2) Popular com alguns valores.
INSERT Categoria VALUES(1,’Frutas’),(2,’Carros’);
INSERT INTO Produto (ID,CategoriaID,Nome,Preco)
VALUES
(1,1,’Maçã’,null),(2,1,’Abacaxi’,2.5),
(3,2,’Tucson’,70000),(4,2,’Fiat Uno’,null);
GO
Vamos agora criar uma view que referencia essas duas novas tabelas e, em seguida, realizar uma operação de DELETE (ou pelo menos tentar).
— Cria a view
CREATE VIEW vwProdutos
AS
SELECT
p.ID
, p.Nome AS Produto — NOTE o uso de ALIAS para a coluna Nome
, c.Descricao AS Categoria — NOTE o uso de ALIAS para a coluna Descricao
, p.Preco
FROM dbo.Produto p
JOIN dbo.Categoria c ON p.CategoriaID = c.ID
GO
— Executando a view e conferindo os resultados
SELECT * FROM vwProdutos;
Agora vamos tentar excluir o registro de ID = 4 (Fiat Uno).
— Deletando o Fiat Uno…???
DELETE FROM vwProdutos WHERE ID = 4;
O SQL Server retornou erro informando que a view não é atualizável porque referencia múltiplas tabelas. Desta forma, não importa se a operação é INSERT, UPDATE ou DELETE.
Vamos alterar a definição da view, fazendo com que referencie apenas a tabela Produto. Iremos, inclusive, remover a coluna Preco (lembre-se que essa coluna não é obrigatória) e, portanto, a view será ainda atualizável.
— Alterando a definição da view
— Removeremos a coluna Preco que não é obrigatória
— Incluiremos a coluna CategoriaID que é obrigatória
— Esta modificação torna a view atualizável
ALTER VIEW vwProdutos
AS
SELECT
p.ID
, p.Nome AS Produto
, p.CategoriaID
FROM dbo.Produto p
GO
Vejamos o que acontece quando executamos o script abaixo.
INSERT INTO vwProdutos (ID, Produto, CategoriaID) VALUES (5, ‘Melancia’, 1);
DELETE FROM vwProdutos WHERE ID = 4;
SELECT * FROM vwProdutos;
Note que tudo funcionou perfeitamente. Uma nova linha foi inserida e o registro de ID = 4 foi excluído.
CHECK OPTION
Esta é uma parte opcional da sintaxe de criação de views e só tem aplicabilidade quando trabalhamos com views atualizáveis. Quando usamos CHECK OPTION o SQL Server forçará qualquer instrução de modificação de dados, executada contra a view, a seguir o filtro definido na cláusula WHERE do SELECT da view.
Vamos rever a sintaxe para a criação / alteração de views:
CREATE VIEW NomeDaView
[ WITH ENCRYPTION / SCHEMABINDING / VIEW_METADA ]
AS
SELECT * FROM Tabela
[ WITH CHECK OPTION ]
GO
Vale destacar que aquilo que aparece entre colchetes é de uso opcional na sintaxe. Atente para o detalhe de que CHECK OPTION é declarado no final da view. Vamos exemplificar o uso dessa opção, alterando a view que criamos em nossos exemplos anteriores.
ALTER VIEW vwProdutos
AS
SELECT
p.ID
, p.Nome AS Produto
, p.CategoriaID
FROM dbo.Produto p
where p.CategoriaID = 2
WITH CHECK OPTION
GO
Conforme a alteração que fizemos, a partir de agora a view retornará exclusivamente produtos da categoria de código 2. Da mesma forma, o uso de CHECK OPTION permitirá inserir/alterar/deletar somente os produtos dessa categoria.
— Tentando inserir um produto de categoria <> 2
INSERT INTO vwProdutos (ID, Produto, CategoriaID) VALUES (6, ‘Jaca’, 1);
Veja que não é possível inserir registro que não atenda ao critério especificado na cláusula WHERE.
— Inserindo um novo veículo
INSERT INTO vwProdutos (ID, Produto, CategoriaID) VALUES (6, ‘FUSCA 77’, 2);
Pronto. Registro inserido com sucesso.
Conclusão
No próximo post estarei abordando exclusivamente o assunto views indexadas e, finalmente, concluindo o tópico 2 do exame 70-461. Dentro em breve publicarei um post contendo material complementar para apoiar os estudos. Quando fecharmos o primeiro módulo – Criando Objetos de Banco de dados (Create Database Objects), disponibilizarei um pequeno simulado abordando esse módulo. Não deixe de acompanhar a série.