update that participates in a join on a view with an instead of up

[shaunmckeag]

I'm receiving an error
"Msg 414 UPDATE is not allowed because the statement updates view <view
name> which participates in a join and has an INSTEAD OF UPDATE trigger."

I've read the SQL Server 2005 documentation on designing Instead of triggers
and no where does it state that this is a restriction.

Is this a bug or is the documentation incorrect? Has anyone ever run into
this? What was your workaround?

Thank you very much for your assistance.

[INTP56]

If I understand this properly, you have something like this:

CREATE VIEW dbo.vTesty AS
SELECT
A.Col1,A.Col2,B.Col3,B.Col4
FROM
dbo.TableA A
INNER JOIN dbo.TableB B
ON A.PK = B.PK

CREATE TRIGGER dbo.tu_vTesty ON dbo.vTesty INSTEAD OF UPDATE AS
BEGIN
-- Whatever ... for kicks, do this
UPDATE
dbo.TableC C
SET
Col1 = i.Col1
,Col2 = i.Col2
,Col3 = i.Col3
,Col4 = i.Col4
FROM
inserted i
WHERE
i.Col1 = C.PK
END

and you are issuing a command like

UPDATE
dbo.vTesty
SET
Col1 = 1
,Col2 = 2
,Col3 = 3
WHERE
Col4 = 4

An INSTEAD OF trigger replaces the original DML with one or more DML
statements, potentially to different tables. The view definition itself only
references TableA and TableB, but the actual update occurs in TableC. SQL
Server cannot know what is going on, because no early checks can be make to
know what actually is going to happen ... until the UPDATE is actually issued.

I don't think there is a direct workaround. You can make additional
views/tables with additional triggers, but that seems like a support problem.

Hope this helps for understanding, even though not for the actual problem.

Bob

[shaunmckeag]

Close..except for the join is in the update statement and not the view
definition. So it would look something like this.

--create a view that does multiple unions on tables partitioned by year
CREATE VIEW dbo.vw_Test AS
SELECT RecordDate,SomeUniqueValue,Col2 FROM tbl_Test_Y2006 WHERE
RecordDate BETWEEN '1/1/2006' AND '12/31/2006'
UNION ALL
SELECT RecordDate,SomeUniqueValue,Col2 FROM tbl_Test_Y2005 WHERE RecordDate
BETWEEN '1/1/2005' AND '12/31/2005'
UNION ALL
SELECT RecordDate,SomeUniqueValue,Col2 FROM tbl_Test_Y2004 WHERE RecordDate
BETWEEN '1/1/2004' AND '12/31/2004'

-- create instead of update trigger to handle update
CREATE TRIGGER dbo.tg_vw_Test_update ON dbo.vw_Test INSTEAD OF UPDATE AS
BEGIN

UPDATE tbl_Test_Y_2006 C
SET
RecordDate = i.RecordDate
,SomeUniqueValue = i.SomeUniqueValue
,Col2 = i.Col2
FROM
inserted i
WHERE
i.SomeUniqueValue = C.SomeUniqueValue
END

--try updating partitioned view with a join statement (something like this)
UPDATE vw_Test
SET Col2 = 'Test'
FROM vw_Test as v
INNER JOIN SomeOtherTable as ot
ON v.SomeUniqueValue = ot.SomeUniqueValue
WHERE ot.Col2 = 'Test2'

--
Sincerely,

Shaun McKeag

[INTP56]

OK, but my explaination still applies. You are trying to UPDATE a view with
an INSTEAD OF trigger. Think of it this way, SQL server does't look at the
code ... it just looks at the fact you are trying to update a view where it's
not sure what is going on.

Instead of this (pun intended)

UPDATE
vw_Test
SET
Col2 = 'Testy'

FROM
vw_Test as v
INNER JOIN SomeOtherTable as ot
ON v.SomeUniqueValue = ot.SomeUniqueValue
WHERE ot.Col2 = 'Test2'

try something like this, which avoids trying to join the 2 tables:

UPDATE
vw_Test
SET
Col2 = 'Testy'
WHERE
SomeUniqueValue IN
(
SELECT
SomeUniqueValue
FROM
SomeOtherTable
WHERE
Col2 = 'Test2'
)

[Sam Bendayan]

Sorry, but I don't understand the explanation. I thought that the very
purpose of an INSTEAD OF trigger on a view was so that you COULD update
the view.

The error is actually stating that you can't update a view WHICH
PARTICIPATES IN A JOIN and has an INSTEAD OF trigger.
I think that maybe the problem has to do with the fact that the view has
a join, but I don't understand what this problem is. Can anyone help?

Thanks,

Sam Bendayan
DB Architect
Ultimate Software
sam.be...@gmail.com

*** Sent via Developersdex http://www.developersdex.com ***

[Hugo Kornelis]

Hi Sam,

You appear to be replying to a message, but I can't find the original
message in my news reader. Could you please repost the original
question?

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Roy Harvey (SQL Server MVP)]

On Mon, 24 Sep 2007 19:45:39 +0200, Hugo Kornelis
<hu...@perFact.REMOVETHIS.info.INVALID> wrote:

>You appear to be replying to a message, but I can't find the original
>message in my news reader.

I found the original messages on Google. The two original posts were
made around 11 months ago.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/528849fe8eead91f/916f12ab0168d88a#916f12ab0168d88a

Roy Harvey
Beacon Falls, CT

[Jim Underwood]

It sounds like the poster is saying you can't have a join in an update
statement, if you are updating a view that uses an instead of trigger.

On the other hand, the join inside the view, and the instead of trigger are
both valid.

It is the update statement itself which is incorrect.

If I understand INTP56 correctly...

"Sam Bendayan" <sam.be...@gmail.com> wrote in message
news:%23jvWI%23s$HHA....@TK2MSFTNGP05.phx.gbl...

[Hugo Kornelis]

On Mon, 24 Sep 2007 14:03:52 -0400, Roy Harvey (SQL Server MVP) wrote:

>On Mon, 24 Sep 2007 19:45:39 +0200, Hugo Kornelis
><hu...@perFact.REMOVETHIS.info.INVALID> wrote:
>
>>You appear to be replying to a message, but I can't find the original
>>message in my news reader.
>
>I found the original messages on Google. The two original posts were
>made around 11 months ago.

(snip)

Thanks, Roy. My access provider has switched its usenet service to a
cheaper provider, so now I am stuck with a lower retention, and I didn't
think of searching Googles archive.

To Sam: You are correct that an INSTEAD OF trigger on a view can be used
to allow updates to a view that would otherwise not allow updates. But
this is purely related to the view definition - so if a join makes a
view not updateable, an INSTEAD OF UPDATE trigger can help you work
around that limitation.

There is, however, a different limitation - one that I have ran into
quick heavily (*) some time ago. For some reason, SQL Server will not
allow a view with an INSTEAD OF UPDATE trigger to be the destination of
an update in the proprietary UPDATE ... FROM syntax. You can still
update the view (that's what the trigger is for!), but you *have* to use
the ANSI-standard UPDATE syntax, as demonstrated in the post that INTP56
posted on Oct 18, 2006.

(*) I found this out because I got to optimize some code that used lots
of UPDATE statements in the ANSI syntax. I changed them all to use the
proprietary syntax (because that syntax is usuallly faster), only to
find out about this limitation. I then had to change them all back,
throwing away three weeks of extremely monotonous work...

I thought I filed a suggestion to lift this limitation on Connect some
time ago, but I couldn't find it so I filed a new suggestion; you might
wish to add your vote at
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299414

[Sam Bendayan]

Well, I'm having the following problem, which is similar:

1. I have a view that joins 2 tables.
2. There is an INSTEAD OF DELETE trigger on the view.
This trigger only deletes data from one of the tables
in the view.
3. When I try to delete from the view, I get the following
error (which is substantially the same as in the
original posting)

Msg 415, Level 16, State 1, Procedure
trBESessionDelLocalDesc, Line 29
DELETE is not allowed because the statement updates
view "dbo.vw_LocalDescriptions" which participates in a
join and has an INSTEAD OF DELETE trigger.

However, I don't understand why this is happening. The trigger has an
unambiguous reference to a single table. Does anyone understand why
this is not allowed?

[Jim Underwood]

Hugo's post explains it better than I could. Basically, the view and the
trigger are probably fine. You have to rewrite the update statement (not
the one in the trigger, the one which causes the trigger to run) to use the
ANSI syntax.

"Sam Bendayan" <sam.be...@gmail.com> wrote in message

news:OX6eFqt$HHA....@TK2MSFTNGP05.phx.gbl...

[Hugo Kornelis]

On Mon, 24 Sep 2007 11:41:28 -0700, Sam Bendayan wrote:

>Well, I'm having the following problem, which is similar:
>
>1. I have a view that joins 2 tables.
>2. There is an INSTEAD OF DELETE trigger on the view.
> This trigger only deletes data from one of the tables
> in the view.
>3. When I try to delete from the view, I get the following
> error (which is substantially the same as in the
> original posting)

Hi Sam,

Can you post the statement you use to delete data from the view (that
would be the statement executed in step 3 above).

[Sam Bendayan]

Got it. The problem was just as you said...on a view with INSTEAD OF
triggers, you can't have a join...when I rewrote the DELETE to use a
EXISTS instead of a join the error went away.

Does anyone have any more background info as to why this is?

Thanks for your help,

[Fred Block]

Hi - I too have this problem with code written by someone esle some time ago. My question is this: If the UPDATE does not directly refer to the view, but a view created from another view, will this still be the cause? For example:

UPDATE wv_productssold
SET cur_yr_lbs...

..and the "wv_productssold" view is actually created from another view named "wv_Details." It happens to be that the 414 error I'm getting refers to the view named: "wv_details" but I cannot find a "direct call to update that view.

I'm kind of at a lost as I've never written complicated SQL like I'm diving into here to troubleshoot but if this is true I'm on the right track.

Thanks! - Fred

From http://www.developmentnow.com/g/113_2006_10_0_0_836067/update-that-participates-in-a-join-on-a-view-with-an-instead-of-up.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

[Erland Sommarskog]

Fred Block (nos...@developmentnow.com) writes:
> Hi - I too have this problem with code written by someone esle some time
> ago. My question is this: If the UPDATE does not directly refer to the
> view, but a view created from another view, will this still be the
> cause? For example:
>
> UPDATE wv_productssold
> SET cur_yr_lbs...
>
> ..and the "wv_productssold" view is actually created from another view
> named "wv_Details." It happens to be that the 414 error I'm getting
> refers to the view named: "wv_details" but I cannot find a "direct call
> to update that view.
>
> I'm kind of at a lost as I've never written complicated SQL like I'm
> diving into here to troubleshoot but if this is true I'm on the right
> track.

And the vw_detalils view has an INSTEAD OF trigger?

To find out exactly where the error occurs, I think the best is to
run Profiler, and include the events Error:Exception and Error:UserMessage
events, as well as events to see which statements and batches that are
executed.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx