[SQL Server] Exame 70 461 – Tópico 2: Criando e alterando views – Parte 3

Views 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.

---