Updatable View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Updatable View

Author

Topic

notmyrealname

98 Posts

Posted - 2009-08-14 : 08:17:11

Hi.

Is it possible to make an updatable view when joining multiple tables?

I am writing an app in VB.Net and because my query includes joined tables ADO.Net will not automatically create Insert, Update and Delete commands. This causes me to have to write my own commands which i can do but i was wondering if i can get a SQL view to allow me to update when using multiple tables.

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-14 : 08:49:44

Read about INSTEAD OF TRIGGER in sql server help file

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-14 : 08:49:44

Yes, as long as the PRIMARY KEY for the table you want to update is present in the view.

CREATE TABLE	Table1
		(
			Col1 INT IDENTITY(1, 1) PRIMARY KEY,
			Col2 INT
		)
GO
INSERT	Table1
SELECT	10 UNION ALL
SELECT	20
GO
CREATE TABLE	Table2
		(
			ColA INT IDENTITY(1, 1) PRIMARY KEY,
			ColB INT REFERENCES Table1(Col1),
			ColC INT
		)
GO
INSERT	Table2
SELECT	1,  1 UNION ALL
SELECT	1,  2 UNION ALL
SELECT	1,  3 UNION ALL
SELECT	2, 10
GO
CREATE VIEW vwView1
AS
SELECT Col1, Col2, ColA, ColB, ColC
FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t2.ColB = t1.Col1
GO
SELECT	*
FROM	vwView1
GO
UPDATE	vwView1
SET	ColC = 4
WHERE	ColC = 10
GO
SELECT	*
FROM	vwView1
GO
UPDATE	vwView1
SET	ColC = -Colc
WHERE	ColC < 4
GO
SELECT	*
FROM	vwView1
GO
UPDATE	vwView1
SET	Col2 = Col2 / 10
GO
SELECT	*
FROM	vwView1
GO
DROP VIEW vwView1
GO
DROP TABLE	Table2,
		Table1

N 56°04'39.26"
E 12°55'05.63"

notmyrealname

98 Posts

Posted - 2009-08-14 : 11:11:14

Hi. Thanks for the replies.

This seems to work ok. I am having a problem with my ado dataset in visual studio. I know this isn't the right place for this but i'm sure some of you are familiar with ado. I wanted to start here because my first question was about creating the updatable view.

If any of you are familiar with ado...

...when i use this view to create a datatable, the datatable will not generate an update command even though the view is in fact updatable. In order to have a datatable with insert, update and delete commands i have to stored procedures to handle these commands. No problem, but i was just hoping that a view would work.

Please let me know if you have any thought or think i should move this thread to another forum.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources