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.

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.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”dXNlIERCRXhhbWU3MDQ2MQpnbwrigJQgMSkgSW5pY2lhbG1lbnRlIGNyaWFyIGR1YXMgdGFiZWxhczogQ2F0ZWdvcmlhIGUgUHJvZHV0by4KQ1JFQVRFIFRBQkxFIENhdGVnb3JpYQooIElEIElOVCBOT1QgTlVMTCBQUklNQVJZIEtFWSwKRGVzY3JpY2FvIFZBUkNIQVIoNTApIE5PVCBOVUxMCik7CkNSRUFURSBUQUJMRSBQcm9kdXRvCiggSUQgSU5UIE5PVCBOVUxMIFBSSU1BUlkgS0VZLApDYXRlZ29yaWFJRCBJTlQgTk9UIE5VTEwsCk5vbWUgVkFSQ0hBUig1MCkgTk9UIE5VTEwsClByZWNvIERFQ0lNQUwoMTUsMikgTlVMTAopOwrigJQgMikgUG9wdWxhciBjb20gYWxndW5zIHZhbG9yZXMuCklOU0VSVCBDYXRlZ29yaWEgVkFMVUVTKDEs4oCZRnJ1dGFz4oCZKSwoMizigJlDYXJyb3PigJkpOwpJTlNFUlQgSU5UTyBQcm9kdXRvIChJRCxDYXRlZ29yaWFJRCxOb21lLFByZWNvKQpWQUxVRVMKKDEsMSzigJlNYcOnw6PigJksbnVsbCksKDIsMSzigJlBYmFjYXhp4oCZLDIuNSksCigzLDIs4oCZVHVjc29u4oCZLDcwMDAwKSwoNCwyLOKAmUZpYXQgVW5v4oCZLG51bGwpOwpHTw==” hover_enabled=”0″]dXNlIERCRXhhbWU3MDQ2MQpnbwrigJQgMSkgSW5pY2lhbG1lbnRlIGNyaWFyIGR1YXMgdGFiZWxhczogQ2F0ZWdvcmlhIGUgUHJvZHV0by4KQ1JFQVRFIFRBQkxFIENhdGVnb3JpYQooIElEIElOVCBOT1QgTlVMTCBQUklNQVJZIEtFWSwKRGVzY3JpY2FvIFZBUkNIQVIoNTApIE5PVCBOVUxMCik7CkNSRUFURSBUQUJMRSBQcm9kdXRvCiggSUQgSU5UIE5PVCBOVUxMIFBSSU1BUlkgS0VZLApDYXRlZ29yaWFJRCBJTlQgTk9UIE5VTEwsCk5vbWUgVkFSQ0hBUig1MCkgTk9UIE5VTEwsClByZWNvIERFQ0lNQUwoMTUsMikgTlVMTAopOwrigJQgMikgUG9wdWxhciBjb20gYWxndW5zIHZhbG9yZXMuCklOU0VSVCBDYXRlZ29yaWEgVkFMVUVTKDEs4oCZRnJ1dGFz4oCZKSwoMizigJlDYXJyb3PigJkpOwpJTlNFUlQgSU5UTyBQcm9kdXRvIChJRCxDYXRlZ29yaWFJRCxOb21lLFByZWNvKQpWQUxVRVMKKDEsMSzigJlNYcOnw6PigJksbnVsbCksKDIsMSzigJlBYmFjYXhp4oCZLDIuNSksCigzLDIs4oCZVHVjc29u4oCZLDcwMDAwKSwoNCwyLOKAmUZpYXQgVW5v4oCZLG51bGwpOwpHTw==[/et_pb_dmb_code_snippet]

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

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”4oCUIENyaWEgYSB2aWV3CkNSRUFURSBWSUVXIHZ3UHJvZHV0b3MKQVMKU0VMRUNUCnAuSUQKLCBwLk5vbWUgQVMgUHJvZHV0byDigJQgTk9URSBvIHVzbyBkZSBBTElBUyBwYXJhIGEgY29sdW5hIE5vbWUKLCBjLkRlc2NyaWNhbyBBUyBDYXRlZ29yaWEg4oCUIE5PVEUgbyB1c28gZGUgQUxJQVMgcGFyYSBhIGNvbHVuYSBEZXNjcmljYW8KLCBwLlByZWNvCkZST00gZGJvLlByb2R1dG8gcApKT0lOIGRiby5DYXRlZ29yaWEgYyBPTiBwLkNhdGVnb3JpYUlEID0gYy5JRApHTwrigJQgRXhlY3V0YW5kbyBhIHZpZXcgZSBjb25mZXJpbmRvIG9zIHJlc3VsdGFkb3MKU0VMRUNUICogRlJPTSB2d1Byb2R1dG9zOw==” hover_enabled=”0″]4oCUIENyaWEgYSB2aWV3CkNSRUFURSBWSUVXIHZ3UHJvZHV0b3MKQVMKU0VMRUNUCnAuSUQKLCBwLk5vbWUgQVMgUHJvZHV0byDigJQgTk9URSBvIHVzbyBkZSBBTElBUyBwYXJhIGEgY29sdW5hIE5vbWUKLCBjLkRlc2NyaWNhbyBBUyBDYXRlZ29yaWEg4oCUIE5PVEUgbyB1c28gZGUgQUxJQVMgcGFyYSBhIGNvbHVuYSBEZXNjcmljYW8KLCBwLlByZWNvCkZST00gZGJvLlByb2R1dG8gcApKT0lOIGRiby5DYXRlZ29yaWEgYyBPTiBwLkNhdGVnb3JpYUlEID0gYy5JRApHTwrigJQgRXhlY3V0YW5kbyBhIHZpZXcgZSBjb25mZXJpbmRvIG9zIHJlc3VsdGFkb3MKU0VMRUNUICogRlJPTSB2d1Byb2R1dG9zOw==[/et_pb_dmb_code_snippet]

IMAGEM – imageimage

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

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”4oCUIERlbGV0YW5kbyBvIEZpYXQgVW5v4oCmPz8/CkRFTEVURSBGUk9NIHZ3UHJvZHV0b3MgV0hFUkUgSUQgPSA0Ow==” hover_enabled=”0″]4oCUIERlbGV0YW5kbyBvIEZpYXQgVW5v4oCmPz8/CkRFTEVURSBGUk9NIHZ3UHJvZHV0b3MgV0hFUkUgSUQgPSA0Ow==[/et_pb_dmb_code_snippet]

IMAGEM – imageimage

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.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”4oCUIEFsdGVyYW5kbyBhIGRlZmluacOnw6NvIGRhIHZpZXcKCuKAlCBSZW1vdmVyZW1vcyBhIGNvbHVuYSBQcmVjbyBxdWUgbsOjbyDDqSBvYnJpZ2F0w7NyaWEKCuKAlCBJbmNsdWlyZW1vcyBhIGNvbHVuYSBDYXRlZ29yaWFJRCBxdWUgw6kgb2JyaWdhdMOzcmlhCgrigJQgRXN0YSBtb2RpZmljYcOnw6NvIHRvcm5hIGEgdmlldyBhdHVhbGl6w6F2ZWwKCkFMVEVSIFZJRVcgdndQcm9kdXRvcwoKQVMKClNFTEVDVAoKcC5JRAoKLCBwLk5vbWUgQVMgUHJvZHV0bwoKLCBwLkNhdGVnb3JpYUlECgpGUk9NIGRiby5Qcm9kdXRvIHAKCkdP” hover_enabled=”0″]4oCUIEFsdGVyYW5kbyBhIGRlZmluacOnw6NvIGRhIHZpZXcKCuKAlCBSZW1vdmVyZW1vcyBhIGNvbHVuYSBQcmVjbyBxdWUgbsOjbyDDqSBvYnJpZ2F0w7NyaWEKCuKAlCBJbmNsdWlyZW1vcyBhIGNvbHVuYSBDYXRlZ29yaWFJRCBxdWUgw6kgb2JyaWdhdMOzcmlhCgrigJQgRXN0YSBtb2RpZmljYcOnw6NvIHRvcm5hIGEgdmlldyBhdHVhbGl6w6F2ZWwKCkFMVEVSIFZJRVcgdndQcm9kdXRvcwoKQVMKClNFTEVDVAoKcC5JRAoKLCBwLk5vbWUgQVMgUHJvZHV0bwoKLCBwLkNhdGVnb3JpYUlECgpGUk9NIGRiby5Qcm9kdXRvIHAKCkdP[/et_pb_dmb_code_snippet]

Vejamos o que acontece quando executamos o script abaixo.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”SU5TRVJUIElOVE8gdndQcm9kdXRvcyAoSUQsIFByb2R1dG8sIENhdGVnb3JpYUlEKSBWQUxVRVMgKDUsIOKAmE1lbGFuY2lh4oCZLCAxKTsKCkRFTEVURSBGUk9NIHZ3UHJvZHV0b3MgV0hFUkUgSUQgPSA0OwoKU0VMRUNUICogRlJPTSB2d1Byb2R1dG9zOw==” hover_enabled=”0″]SU5TRVJUIElOVE8gdndQcm9kdXRvcyAoSUQsIFByb2R1dG8sIENhdGVnb3JpYUlEKSBWQUxVRVMgKDUsIOKAmE1lbGFuY2lh4oCZLCAxKTsKCkRFTEVURSBGUk9NIHZ3UHJvZHV0b3MgV0hFUkUgSUQgPSA0OwoKU0VMRUNUICogRlJPTSB2d1Byb2R1dG9zOw==[/et_pb_dmb_code_snippet]

IMAGEM – imageimage

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:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”Q1JFQVRFIFZJRVcgTm9tZURhVmlldwoKWyBXSVRIIEVOQ1JZUFRJT04gLyBTQ0hFTUFCSU5ESU5HIC8gVklFV19NRVRBREEgXQoKQVMKClNFTEVDVCAqIEZST00gVGFiZWxhCgpbIFdJVEggQ0hFQ0sgT1BUSU9OIF0KCkdP” hover_enabled=”0″]Q1JFQVRFIFZJRVcgTm9tZURhVmlldwoKWyBXSVRIIEVOQ1JZUFRJT04gLyBTQ0hFTUFCSU5ESU5HIC8gVklFV19NRVRBREEgXQoKQVMKClNFTEVDVCAqIEZST00gVGFiZWxhCgpbIFdJVEggQ0hFQ0sgT1BUSU9OIF0KCkdP[/et_pb_dmb_code_snippet]

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.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”QUxURVIgVklFVyB2d1Byb2R1dG9zCgpBUwoKU0VMRUNUCgpwLklECgosIHAuTm9tZSBBUyBQcm9kdXRvCgosIHAuQ2F0ZWdvcmlhSUQKCkZST00gZGJvLlByb2R1dG8gcAoKd2hlcmUgcC5DYXRlZ29yaWFJRCA9IDIKCldJVEggQ0hFQ0sgT1BUSU9OCgpHTw==” hover_enabled=”0″]QUxURVIgVklFVyB2d1Byb2R1dG9zCgpBUwoKU0VMRUNUCgpwLklECgosIHAuTm9tZSBBUyBQcm9kdXRvCgosIHAuQ2F0ZWdvcmlhSUQKCkZST00gZGJvLlByb2R1dG8gcAoKd2hlcmUgcC5DYXRlZ29yaWFJRCA9IDIKCldJVEggQ0hFQ0sgT1BUSU9OCgpHTw==[/et_pb_dmb_code_snippet]

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.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”4oCUIFRlbnRhbmRvIGluc2VyaXIgdW0gcHJvZHV0byBkZSBjYXRlZ29yaWEgPD4gMgoKSU5TRVJUIElOVE8gdndQcm9kdXRvcyAoSUQsIFByb2R1dG8sIENhdGVnb3JpYUlEKSBWQUxVRVMgKDYsIOKAmEphY2HigJksIDEpOw==” hover_enabled=”0″]4oCUIFRlbnRhbmRvIGluc2VyaXIgdW0gcHJvZHV0byBkZSBjYXRlZ29yaWEgPD4gMgoKSU5TRVJUIElOVE8gdndQcm9kdXRvcyAoSUQsIFByb2R1dG8sIENhdGVnb3JpYUlEKSBWQUxVRVMgKDYsIOKAmEphY2HigJksIDEpOw==[/et_pb_dmb_code_snippet]

IMAGEM – imageimage

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

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”4oCUIEluc2VyaW5kbyB1bSBub3ZvIHZlw61jdWxvCgpJTlNFUlQgSU5UTyB2d1Byb2R1dG9zIChJRCwgUHJvZHV0bywgQ2F0ZWdvcmlhSUQpIFZBTFVFUyAoNiwg4oCYRlVTQ0EgNzfigJksIDIpOw==” hover_enabled=”0″]4oCUIEluc2VyaW5kbyB1bSBub3ZvIHZlw61jdWxvCgpJTlNFUlQgSU5UTyB2d1Byb2R1dG9zIChJRCwgUHJvZHV0bywgQ2F0ZWdvcmlhSUQpIFZBTFVFUyAoNiwg4oCYRlVTQ0EgNzfigJksIDIpOw==[/et_pb_dmb_code_snippet]

IMAGEM – imageimage

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.