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

SQL Server Development (2000)

error help

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-29 : 18:56:54

Hey all,

just wondering why i cant do this

i am perofmring an update on one of my views from within an after update statment ( just a note that the view has a instead of update trigger) is this not allowed?

this is the error

Server: Msg 4422, Level 16, State 1, Procedure ADJUSTDIMONDEFINEUPDATE, Line 15
View 'dbo.ENTITY_ITEM_VIEW' has an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM statement.

and this is the update

update dbo.ENTITY_ITEM_VIEW
set TotalDimElements = TotalDimElements / d.Evaluated * case when i.ValueIsInt = -1 then i.Evaluated else 1 end
from dbo.ENTITY_ITEM_VIEW e
JOIN inserted i on e.MajorBuildNo = i.MajorBuildNo AND e.MinorBuildNo = i.MinorBuildNo
JOIN deleted d on e.MajorBuildNo = d.MajorBuildNo AND e.MinorBuildNo = d.MinorBuildNo
WHERE e.entityitemID in (SELECT EntityItemId FROM dbo.dimension
WHERE defineid in (SELECT defineid FROM inserted)
AND MajorBuildNo = 0 AND MinorBuildNo = 0)
AND i.MajorBuildNo = 0 AND i.MinorBuildNo = 0
AND d.evaluated <> i.evaluated AND e.MajorBuildNo = 0 AND e.MinorBuildNo = 0

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-29 : 19:17:51

>> after update statment ( just a note that the view has a instead of update trigger) is this not allowed?

The error message says it's not - I would believe it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-29 : 20:13:37

can anyone explain to my why sql server wouln't let you do a
update...from and use a table that has a instead of update trigger as a target of the update...from statment?

i dont quite get why this would be prevented by sql server?

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-29 : 20:18:24

or better yet,
can anyone tell me how i might go about getting this situation to work?

anyone know how i can get the update statment to work in a similar way withouth using the entity_item_view in the target of the from clause?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-29 : 20:20:46

BOL says this and nothing more:

quote:

A view with an INSTEAD OF UPDATE trigger cannot be a target of an UPDATE with a FROM clause.

So the workaround would be to update the table directly and not through the view.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-29 : 20:49:08

Or loop through the rows updating with variables.
yuk!

Or you could try using subqueries in the set clause - but I suspect that might cause problems - depends on why this restriction was imposed.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-30 : 17:38:59

thanks all,

seems to work with subqueries which is a bit odd that it didn't let me do it the other way :) oh well hehe

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources