Um blog sobre nada

Um conjunto de inutilidades que podem vir a ser úteis

Inserting Data through views

Posted by Diego em Agosto 6, 2013


How to insert data through a view that access more than one table:

 

1)  Create 2 simple tables and a view that joins both:

 

CREATE TABLE TABLE1

(id INT, NAME VARCHAR(20))

GO

 

CREATE TABLE TABLE2

(id INT, LastNAME VARCHAR(20))

GO

 

 

CREATE VIEW name_lastname AS

SELECT t1.id, t1.NAME, t2.lastname

FROM table1 t1 JOIN table2 t2

ON t1.id = t2.id

GO

 

2)  Create 2 views that access table 1 and 2:

 

CREATE VIEW vwt1

AS SELECT id, name FROM table1

GO

 

CREATE VIEW vwt2

AS SELECT id, LastNAME FROM table2

Go

 

3)  As you can see it’s possible to insert data on TABLE1 and TABLE2 using the individual views:

 

 

INSERT INTO dbo.vwt1 (id, name) VALUES (1, ‘name 1’)

INSERT INTO dbo.vwt2(id, LastNAME) VALUES (1, ‘lastname1’)

 

 

SELECT * FROM name_lastname –OK

 

4)  But, If we try to insert data through the main view, we get an error:

 

 

INSERT INTO dbo.name_lastname (id, NAME, lastname)

VALUES(2,‘name2’, ‘last name 2’)

 

 

Msg 4405, Level 16, State 1, Line 1

View or function ‘dbo.name_lastname’ is not updatable because the modification affects multiple base tables.

 

5)  The way to avoid this is to create a INSEAD OF INSERT trigger on the view and deal with the individual inserts inside the trigger:

 

 

create TRIGGER t_insert_name_lastname

ON name_lastname

INSTEAD OF INSERT

as

begin

  INSERT INTO dbo.TABLE1 (id, NAME) SELECT id,NAME FROM INSERTED

  INSERT INTO dbo.TABLE2 (id, lastname) select id,lastname FROM inserted

End

GO

 

 

INSERT INTO dbo.name_lastname (id, NAME, lastname)

VALUES(2,‘name2’, ‘last name 2’)

GO

  

INSERT INTO dbo.name_lastname

(id, NAME, lastname),

SELECT 3, ‘name 3’, ‘last name 3’

UNION

SELECT 4, ‘name 4’, ‘last name 4’

UNION

SELECT 5, ‘name 5’, ‘last name 5’

 

 

SELECT * FROM name_lastname
 

clip_image001[4]

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

 
%d bloggers like this: