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

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.

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.

Vamos agora criar uma view que referencia essas duas novas tabelas e, em seguida, realizar uma operação de DELETE (ou pelo menos tentar).

IMAGEM – image

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

IMAGEM – 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.

Vejamos o que acontece quando executamos o script abaixo.

IMAGEM – 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:

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.

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.

IMAGEM – image

Veja que não é possível inserir registro que não atenda ao critério especificado na cláusula WHERE.

IMAGEM – 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.