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;

image

Agora vamos tentar excluir o registro de ID = 4 (Fiat Uno).

— Deletando o Fiat Uno…???
DELETE FROM vwProdutos WHERE ID = 4;

image

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;

image

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

image

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

image

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.