is a view insertable/updateable ?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

is a view insertable/updateable ??

Author

Topic

Tim
Starting Member

392 Posts

Posted - 2002-05-10 : 02:00:25

Hi guys,

I have a list of views, and I need to programmtically determine if each one allows inserts and/or updates.

What's the best way ?

Cheers,
Tim.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-05-10 : 03:05:46

select * from INFORMATION_SCHEMA.Views

has a columns called IS_UPDATEABLE

I reckon that could be a winner

Damian

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-10 : 05:09:14

I don't think that will work. The IS_UPDATEABLE column always returns NO.
Try it out with a simple one table view. It even says so in BOL.

It is the actual modification statements, that will determine if a view is updateable.
For example "Data modification statements (INSERT and UPDATE only) are allowed on multiple-table views if the data modification statement affects only one base table"

There is a list of these rules in Inside MS SQL Server 2000 (Chaper 7). You will probably find similar elsewhere.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-10 : 11:09:43

SQL server 7.0 books say that views are select only... that help? Views really only help people selecting or VIEWing data. Since views can hold calculated numbers for some of their columns, they were never intended to be updated.

If your going to update or insert just use multiple inserts/updates on the tables themselves

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-10 : 11:13:34

quote:
SQL server 7.0 books say that views are select only

Normally that's true, however there are circumstances under which views CAN be updated. The Books Online entries on views has information on these circumstances.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-10 : 11:23:03

For sql server 7.0? or is this a new feature in sql 2k?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-10 : 11:27:59

Nope, it's been possible since at least SQL 6.5, but the favorable conditions have broadened with each version, so the same view may be updatedable in 2000 but not in 7.0 or 6.5.

Tim
Starting Member

392 Posts

Posted - 2002-05-12 : 20:11:53

So no answer is the answer?

BOL says that views are insertable and/or updateable under certain circumstances. Trouble is the set of circumstances is long-ish and depends on the syntax of the query behind the view. That is, does it use aggregate functions?, does it update columns from only one base table? and on and on...

I could maybe query sys_comments and look for all the restrictions in the query's code, but that is messy and prone to error.

I can't find anywhere that I can determine this programmtically.

Last resort is to attempt a dummy update/insert and see if it fails or not! arrghhh

Any more ideas?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-05-12 : 20:41:34

quote:

Since views can hold calculated numbers for some of their columns, they were never intended to be updated.

They where ALWAYS intended to be updatable...

Instead of calling it a view, if we said it was a virtual relation, would that help?

The whole point of a view is data abstration and independance.. Views (should) allow us to alter the underlying table(s) schema without affecting the end user..

eg.
Lets say we have a customer table.


create table Customers(CustomerID int not null Primary Key, CustomerName varchar(150) not null, Address varchar(50)

And its view...


create view vwCustomers
as
Select CustomerID, CustomerName, Address from Customers

The "user" just sees this view only and can Insert/update/delete etc...

Now further down the line, some user says "Each Customer needs more than 1 Address"

Now how can we change the Customer table without rewriting parts of the application?

With proper support for views it would be a breeze...

Make our New table...


Create table CustomerAddresses(CustomerID int not null, Address varchar(150) not null constraint PK_CustomerAddresses Primary Key (CustomerID, Address) Foreign key blah.. blah...)

Alter the view


Alter view vwCustomers
as
Select C.CustomerID, C.CustomerName, CA.Address from Customers C
inner join CustomersAddress CA on CA.CustomerID = C.CustomerID

Wouldn't that be nice!

SQL Server 2K has "INSTEAD OF" triggers that can do this...if only it was native....

DavidM

"SQL-3 is an abomination.."

Tim
Starting Member

392 Posts

Posted - 2002-05-12 : 20:50:25

Hey byrmol, Nice sermon but your preachin' to the choir !!!

Any ideas on solving the problem of programmatically finding out if the view allows update and/or insert ?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-05-12 : 21:03:07

quote:

Any ideas on solving the problem of programmatically finding out if the view allows update and/or insert ?

Only your suggestion to "Insert" and trap errors...

That is programmatic, however ugly

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources