Inserting or Updating a View

In Sql 2000 or in Sql 2005, what is the best way to INSERT or UPDATE a View?
For example, I have three tables: zWoof, zSonOfWoof and zGrandsonOfWoof.
Woof has Columns WoofID(key) and WoofName(nvarchar).
SonOfWoof has cols SonOfWoofID, WoofID and SonOfWoofName.
GrandsonOfWoof has Cols GrandsonOfWoofID, SonOfWoofID, and
GrandSonOfWoofName. (with z's prefixed, etc... ( just like dogs.))
I make a view:
SELECT dbo.zWoof.WoofID, dbo.zWoof.WoofName, dbo.zSonOfWoof.SonOfWoofID,
dbo.zSonOfWoof.SonOfWoofName,
dbo.zGrandSonOfWoof.zGrandsonOfWoofID,
dbo.zGrandSonOfWoof.zGrandSonOfWoofName
FROM dbo.zWoof INNER JOIN
dbo.zSonOfWoof ON dbo.zWoof.WoofID =
dbo.zSonOfWoof.WoofID INNER JOIN
dbo.zGrandSonOfWoof ON dbo.zSonOfWoof.SonOfWoofID =
dbo.zGrandSonOfWoof.zSonOfWoofID
But I can not find a way to use a Stored procedure to insert a column to
GrandsonOfWoof, for example.
Any recommendation would be greatly appreciated.. (Do I use an "Indexed
View"?)
TIA,
Paul
(woof!)On Thu, 26 Jan 2006 10:32:09 -0500, Paul wrote:

>In Sql 2000 or in Sql 2005, what is the best way to INSERT or UPDATE a View
?
>For example, I have three tables: zWoof, zSonOfWoof and zGrandsonOfWoof.
>Woof has Columns WoofID(key) and WoofName(nvarchar).
>SonOfWoof has cols SonOfWoofID, WoofID and SonOfWoofName.
>GrandsonOfWoof has Cols GrandsonOfWoofID, SonOfWoofID, and
>GrandSonOfWoofName. (with z's prefixed, etc... ( just like dogs.))
>I make a view:
>SELECT dbo.zWoof.WoofID, dbo.zWoof.WoofName, dbo.zSonOfWoof.SonOfWoofID
,
>dbo.zSonOfWoof.SonOfWoofName,
> dbo.zGrandSonOfWoof.zGrandsonOfWoofID,
>dbo.zGrandSonOfWoof.zGrandSonOfWoofName
>FROM dbo.zWoof INNER JOIN
> dbo.zSonOfWoof ON dbo.zWoof.WoofID =
>dbo.zSonOfWoof.WoofID INNER JOIN
> dbo.zGrandSonOfWoof ON dbo.zSonOfWoof.SonOfWoofID =
>dbo.zGrandSonOfWoof.zSonOfWoofID
>But I can not find a way to use a Stored procedure to insert a column to
>GrandsonOfWoof, for example.
>Any recommendation would be greatly appreciated.. (Do I use an "Indexed
>View"?)
>TIA,
>Paul
>(woof!)
>
Hi Paul (meow),
You can't insert rows in the tables through this view. Since the view
shows data from three tables, an INSERT might be equivalent to an INSERT
in all three base tables - and that is not supported.
If you can just insert into the base tables, do so.
If you really *have* to insert through this view, then check out INSTEAD
OF triggers. They are described in Books Online. If the help there is
not enough to get you going, then by all means come back here for more
help - but in that case, I'll have to ask you to use CREATE TABLE and
INSERT statements to explain your situation. See www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP